The Ocean Hotel mission in Vampire: The Masquerade - Bloodlines scared me.
I loved that level for its scaryness. Well actually, there are only two parts that scared me. When the ghost of the woman runs past you saying "Help me!" or something similar.. and when you find the newspaper clip about a child's head that's been found in the laundry room....when you stop reading the clip, the washing mashine suddenly starts.
It took me a while to go in the laundry room.

Doom 3 gave me a pretty good scare once. The only one actually since the game is all about monsters ambushing you from everywhere. When all hell breaks loose at the start, if you go in the first restroom and look at your reflection, http://www.youtube.com/watch?v=jP2uym0wX-s
I was just running fast from room to room, happy to kill enemies and that cutscene really surprised me. And made me quit the game. It's the only time a game did this to me. And surprisingly, it's Doom 3 which, as I said, I don,t find scary at all.

Another good scare that actually still gives me the goosebomps whenever I think about it or that I watch it again is in System Shock 2. (Spoiler ahead!)
Spoiler http://www.youtube.com/watch?v=NOFZ5fv_pb8
I was literally frozen when that happened in the game. Highly surprised that I was tricked all along and [censored] scared because I didn't feel powerful anymore. My guns and powers were now useless and SHODAN had won.
One of my favourite scenes in gaming history.

Otherwise, Silent Hill 1-2-3-4, Penumbra and Amnesia are my other favourite scary games. FEAR 1 wasn't scary at all when you noticed its pattern (Scary part-Battle part-scary part-battle part), but was a pretty good shooter nonetheless.