After claiming the throne of madness, a quest journal will pop up commenting on your advancement towards godhood. You will receive three lore-friendly spells as soon as you load your save file: Intangibility, Physical Form and Daedric Weakness. Consider this the ultimate endgame reward the devs never added upon finishing the SI expansion.
Even if you finish the expansion before the base game, the effect remains more or less aligned with the lore. The dragonfires remain somewhat active right before you face Dagon, despite being weak.