For me the two greatest moments were:
First seeing the Dark World atop the Pyramid of Power right after Agahnim sends you there. The music, the epic scene of Death Mountain on the horizon behind you, it's the point in the game where you know it's only really beginning.
The TF talking to you after defeating Ganon, and finally making the best wish that has ever been wished come true. (though
SS and
AoL Link had some pretty good wishes. Boo king Daphnes!)
A third would be getting the MS, but that's been mentioned and pretty obvious.