I can think of a few (some already mentioned):
Journey: Collapsing on the snowy mountain, and seeing your partner falling in the snow beside you as they valiantly try to make their way ahead.
Deus Ex: The "Kill Bob Page" ending, which ends on the hand holding the globe and Morgan Everett saying, "...and together, we will lead them into the day."
Deus Ex: Human Revolution: The Jaron Namir boss fight in the room of bodies, complete with alternate cutscenes (whether or not you got the Biochip) and Namir's speech - "You've lost her, Jensen. Men like us? We never get back the things we love."
Super Metroid: The completely non-obtrusive way the game introduces you to walljumping, via the creatures in the passage demonstrating it. No cutscenes or asides whatsoever - they just keep doing it, and you'll realize you can try it to. Breaks the game wide open, and is completely immersive.
Infamous 2: The "Kill The Beast" ending - the "Let go" shot in front of the Church with John broken down in the background.
Mass Effect 2: A toss-up between controlling Shepard as s/he moves through the depressurizing Normandy in the prologue, or the whole Omega-4 Relay sequence (with all its variables).
Mass Effect 3: Shepard looking up at the Shroud as it collapses, then seeing the genophage cure on his/her hand as Wrex walks into the sunlight and looks up and "Vigil" plays. May not have made the entire game art, but made the scene much better than the rest of the material.
Metal Gear Solid 2: The moment in the ending when Raiden truly breaks the fourth wall for good and throws his dogtags (inscribed with the player's name) away, with the remark that he'll be his own man.
Final Fantasy VI: So many moments, but I've always loved the sequence in Setzer's crypt for Darryl - learning about the two of them and their rivalry, discovering the second airship, and breaking the waves out of the ocean as "Searching For Friends" triumphantly plays and you get access to the world map. It's beautiful.