I think by far my favorite moment was running up the steps during one of the last main quest parts and just cutting down the common Draugr (one hit one kill). When I got to the top, I looked down the steps and realized just how BA and action hero like that was (I never stopped to loot), and made me feel really powerful (i was about lvl 20 or so).
For me, the most epic moment was a time when Esbern, the other blade-member character lady (I don't remember her name), and I, were supposed to find an ancient cave hidden in the hills. We had split up, and I found out that blocking the way to the cave was a huge forsworn base... It was half made up of docks across a river, and half made up of an old crumbling structure of some sort.
Unaware that my allies had to cross the same obstacle, I snuck around back across the water to take the forsworn group by surprise. As I came out on the other side of the river, I saw magic lighting up the night, and noticed Esbern and my other friend on the other side, battling off the forsworn as well. Eager to crush my foes between us, I ventured onto the docks and drew my bow, dropping an enemy or two just before I heard a roar, and saw a frost dragon thu'um a few forsworn into the river.
The three-way battle between us, the dragon, and the forsworn lasted almost until dawn... it was definitely the most epic battle I had. The sad part of this all is that this character- my level 34 nord archer, got accidentally deleted somehow, and now I'm stuck with a boring low level... :(
Yeah, that was an epic battle! It was definatly cool coming over that ridge and seeing all that action going on. It wasn't night time for me though, but still it was cool. My first thought was "what the hell is going on here? there's some baddass people down there fuckin shit up!" I was pretty far away and wasn't expecting that. All I could see was 'magic' going on....alot! And of course, I was hungry for a little blood myself and couldn't resist to join my fellow companions.
Was out on some quest or other and trying to climb a mountain. Rounded a ridge line and found myself high above the Laberenthiam (sp?). Had no idea it was there or that it was part of a quest I had yet to start. The view was remarkable. Love the game designers' ability to hide remarkable ruins/forts/etc. into the terrain.
The exact same thing happened to me-nighttime, dragon, three-way battle. I had broken off from doing the main quest at that point and had been doing other stuff for a while, so I completely forgot that Esbern and Delphine were supposed to be there. I agree, possibly the most intense battle in the game. Way better than Alduin, anyway.
On a side note: I swear, those forsworn come out of nowhere. We had them all cleaned up, I put my sword away, and was walking toward the objective when suddenly a Hargraven and 6 more forsworn appeared. Where the hell were they hiding during the giant battle?!
I think one moment that really stands out in my mind is when I did the Gildergreen Quest the first time. That cave you go to with the Gildergreen tree in was amazing. I can remember just staring at the beauty of the place. Other than that I would have to say the Dark brotherhood comes close to the top. The missions, the whole diving into the night mothers coffin and saving the brotherhood from being burnt down to the big decision whether or not to kill Cicero. All brilliant.
Quite recently my level 64 character had an amazing battle in the rocky plains area near Riften. I fought 2 Ancient Dragons and a Revered Dragon at the same time, alongside Odahviing and Durnehviir. Took almost half an hour (I play on Legendary) but the satisfaction of defeating the three dragons easily made up for that. Oh, and I leveled up twice during the fight :P