Other honorable mentions:

Modern Warfare 2's No Russian level. It's the first time playing a video game where I had to just pause and put my controller down for a few minutes and think about why I pressed the trigger when the others opened fire.

Only game ever that shook me a bit was a cutscene in Command and Conquer where you see the silhouette (through a frosted glass window, I think) of one man shooting another man tied to a chair in the head. A brutal, realistic depicition of an execution.

To this day I'm still surprised I made it out of that school level.

I wouldn't say it "disturbed" me, but House of the Dead: Overkill on Wii definitely turned me off. I realize they were trying to hit that "grindhouse" vibe, but it was such a ham-fisted attempt to do so that I just set it aside after an hour or so. It felt like a 12 year-old's idea of "mature" content, with naughty…

Gore? Blood? That never disturbed me.

Far Cry 2 made me stop and pause the game for several minutes. I was sniping from a hill to take out an enemy outpost. I hit one guy in the leg, incapacitating him. I thought he would just crumple over like any other game enemy does after a few seconds, but then I saw another enemy run over to him and start to drag…

First Deadspace was pretty grood at messing with my state of mind. Was always paranoid necromorphs were around the corner.

Well obviously the amazing Full Throttle intro requires a nomination. The Gone Jackals!

Parasite Eve Opera Scene:

I'm going to throw Batman: Arkham City out there. Both City and Asylum integrate fantastic playable sequences into the opening credits, but City takes it the extra mile by establishing just how dangerous and deranged of a character Strange is and how screwed up Arkham City is.

I nominate Fallout 3.

If you were a 40K fan prior to 2004, I can guarantee you that nothing excited you more than this:

I've always been partial to the intro to Soul Edge:

No Contest

I don't know that it's my favorite favorite, but Borderlands had a pretty great opening that was succinct, catchy, and a good impression of what you were about to get.

Mass Effect 2 if that counts.