Mass Effect 3's intro had the most profound effect on me, which is only strengthened by the effect it has on the character throughout the game too. I'm not going to post a video of the whole thing, because it's about 20 minutes long (most of it you're playing though), but it ends with this:
(this'll spoil things if you haven't played through Mass Effect 1 and 2 yet (if so, what the hell are you waiting for?))
(Wrong Shep though. FemShep or no Shep!)
The music is so perfectly orchestrated to the scene, which adds so, so much to it.