As soon as I clicked on this thread, I knew the first response would be Amnesia: The Dark Descent. I was right.

I'm going to say the same answer, too. The first two Penumbra games are up there, too. Especially the part in Black Plague where
Spoiler you give that guy the saw and he cuts off his arm and comes at you.
Shalebridge Cradle from Thief and that hotel level from Vampire: The Masquerade: Bloodlines looked scary, but I've never played either game.
Also, when I was a little kid, the wallmasters from Legend of Zelda: Ocarina of Time used to scare the crap out of me. There was also a pretty terrible game based on Beast Wars . It's so stupid now that I'm older, but man, it's so dark and desolate and it has scary pixelated dinosaurs.

http://www.youtube.com/watch?v=-5dBu_A924w
Just look at that and tell me you see why that scared a 4-year old.

I wish I could find footage of this one level. You were in a corridor and then this HUGE thing came at you really fast. Oh, man.