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.
Ilend Vonius, one of the surviving guards, survived inside Oblivion by fighting off Daedra for who knows how long while the rest of his comrades were killed on the bridge to the tower. He was kicking demon ass for hours before you showed up. He also made it out of the city with the rest of the surviving guards, which is no small feat, either.
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. Once he reaches the Temple of the One in the Imperial City, he shatters the Amulet of Kings, transforms into a giant flaming dragon, and sends Mehrunes Dagon back to Oblivion before turning into a statute.
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.
"This individual is not to be trifled with." The game is full of people falling all over themselves to compliment you, but there's no compliment like one passed privately between foes.
Here's one for one of the Dark Brotherhood Quests fittingly called "Whodunnit?". Ever remember reading or hearing stories about people being trapped in a place with a killer hiding amongst them and killing them one by one? Given this is a Dark Brotherhood Mission, it stands to reason that you, the player, will be playing the murderer and you receive the bonus if you aren't found out. Not to mention the multiple ways you can manipulate people to be alone where you can pick them off or even turn them against each other is very delicious to play with.