The entire battle for Kvatch is one of these in spades, especially the first time you step through an Oblivion Gate. Not many games have you storming the gates of Hell and kicking demon ass at Level One.
Once you kill Mankor Camoran, his 'Paradise' starts falling to pieces and you're teleported back to Cloud Ruler Temple, the Amulet of Kings in your hand. As the portal dissolves, Jauffre says 'Blades! Pay homage to Martin's champion!' And every single one of them will bow before you.
Stealing an Elder Scroll from the heavily guarded Imperial Palace.
Martin becoming the Avatar of Akatosh, banishing Mehrunes Dagon into Oblivion, and sealing it off forever.
Completing Shivering Isles' Main Quest and becoming the new Sheogorath.
The scope of the Shivering Isles' awesome crowning requires an elaboration. As a witless stranger, you enter an alien world, ruled by weird rules understandable only to a mad god. There you are recruited by Sheogorath, the Daedric Prince of Madness, as his champion — the only hope for his realm... the culmination of everything that he has worked for and accomplished for an era. One task by one, you struggle to suit your new title and gradually earn the respect and esteem of an entire world. As the world crumbles, you fight the invaders, only to realize that this is all due to Sheogorath's eternal curse to destroy himself every era. In the end, the plan to save the Shivering Isles fails and Sheogorath is transformed into Jyggalag, the god of colorless nothing, set to destroy his own life's work. But you don't give up — you fight. You fight a world-destroying demon-god... and win. For your achievement and contribution to the Shivering Isles and for freeing a god from the curse of eternal suicide, you are raised to godhood. The Shivering Isles had a crowning plot of awesome.