Fatal Frame was a damn creepy game, I forgot all about that one. Especially that one ghost of the blindfolded woman in the Kimono, with the cloth over her eyes and the blood running out from underneath it and she constantly screams about her eyes being gone. Then there was the ghost of the woman who had to float backwards because her head was completely snapped back. Brrrrrrrrr

Aside from what's been mentioned (Silent Hills, Dooms, DCoTE, SS2, etc... all with good-to-amazing sound design I might add), I always thought Shadowman was very effective at creating a disturbingly tense atmosphere, although not downright "scary" since the player character was just as dark and grotesque as everything else in the world.
That said, I didn't blink much while running around The Asylum...
edit: now with 10% more descriptive!

Games like Doom 3 or F.E.A.R. don't scare me (something that jumps out in front of you). Games like Silent Hill or System Shock 2 scare me (creepy atmosphere or the sound of monkeys around the corner).

Fear bothered me because you couldn't kill the girl, Manhunt scares me because the idea is so close to reality (some rick freaks will do ANYTHING for more money eh?) Duke 3d had some bad moments, but my worst by far has got to be halo 2 the library level, they just didn't stop coming!!! first play through only after that i had more than enough ammo for that level...

Doom 3, because badass monsters jump out and attack you. It's real bad when you're playing on more difficult modes because those 'surprise' attacks can devastate you!
System Shock 1, because you're never safe. You can kill everything on the level but you'll still get attacked.
Doom 1 and 2 scared me when I was a kid, just because, you know, demons and all.

I think that Fahrenheit (Indigo prophecy in US) was good in this. That paranoic and "what the fuck is happening to me?" feeling was quite strong. And some scenes like reflection of murdered man in Lucas's mirror didn't make it any easier. It was not really "scary", but really hard atmosphere of despair and confusion...i liked that :)

Despite being a huge fan of HP Lovecraft, I never played any of the CoC games. Were they really that freaky? Kind of a silly question considering the source they came from, Lovecraft was a twisted bastard, lol. I'm really starting to hope those games show up on here so I can play them now.

Ya, CoC: DCotE can mess with your brains, especially if you're into Lovecraft and his creepy friends. Don't know about Sanitarium, though. When I played it for the first time nine years ago I was expecting to be scared out of my pants. Instead I ended up crying like a little baby. It can get a little creepy sometimes, but the feeling I've got was that of a great sadness. It never happened to me in a game to actually *feel* the sorrow of the main character. Not on this level, anyway. Never before and only once after (in Planescape: Torment). Now I'm nine years older (almost thirty) but I still can't play The Mansion level without almost bursting into tears.
I must say the scariest game I've played was Clive Barker's Undying. I'm not quite a fan of Mr Barker (liked some of his stories, but that's all), but the game was brilliant. And the funny thing is that a weapon (ya, it's the tibetan "bazooka") manages to scare you more than the whole bunch of monsters :)
Well, Shadow of the Comet (the game who got me into the Mythos, so to speak) is your standard adventure game with more than a touch of Lovecraft delightful creepiness. It's creepy, it makes you feel uneasy, like you're being watched by the cousin of Yog Sottoth's, it's very lovecraftian but it's not scary (not "boo" scary anyway). Dark Corners of the Earth it's the game Lovecraft would've made if he were a game designer. And alive, of course :) It is like being thrown into a Lovecraft story without the possibility to get out. Did you have one of those dreams where you were followed by some unknown horror and you were moving in slow motion? That's what DCotE feels like. Buy it, play it, treasure it for I don't think you'll se something like that too soon. For those who've played the game, the hotel chase is one of the best moments in gaming

I would have to say that Clive Barker's Undying was an incredibly scary game. I definately jumped about 10 million times too while playing both Alien Vs. Predator games. Sanitarium was super creepy since I was pretty young when I played it. The original Quake had it's moments too

F.E.A.R. was really scary. I do not play scary games like that normally, but once a while there come a game that is really good in some way and I have to play it. Even if it is a scary game.
I haven't seen Eternal Darkness being mentioned here. It might not be that scary, but it has a creepy atmosphere, and the way insanity is being used is brilliant. There have been times I have been scared playing it. Not the "monsters jump you" scared, but the "damn, I don't want to move out of the safe little space" kind of scared.

I've never really classified a game as out-and-out scary, but there have been many creepy edge-of-your seat tense games, and some of those also have the jump back from the screen when something jumps out at you. Ones that come to mind:
Certain portions of half-life. To some extent, half-life 2 as well. Damn headcrabs.
Bioshock-creepy atmosphere, hearing all the splicers rant, and though it's not "scary" the brainwashing of the little girls disturbed me a bit.
Silent hill. the first one was intense, the second one, with the rape thing... was just wrong.
F.E.A.R. I have to say I shot at pretty much anything that moved in that game, it had such a tendency to startle me, and the soundtrack was great at building up tension.
Doom 3. When what I thought were 2 red lights on a wall blinked and went out, then I couldn't find the demon... that was creepy.
Condemned: Criminal Origins was creepy at first, but it just got old, and then the storyline went to hell...

I have to agree that of modern gaming Call of Cthulhu is the No.1 scary game. A close second is the first Blair Witch game - walking down that street with the lightning storm looking for something that just woke you up.well....!
But first scares are always th most memorable - so very fearful for me at a younger age was Forbidden Forest on the C64 and an early TEXT game from Infocom, called The Lurking Horror! There's something about having to type OPEN THE DOOR as opposed to reading in a book 'He scarily opened the creaky door'!!!
I also have to give a vote for a different sort of fear created by X-Com, with the eternal worry of losing an experienced team member of a dozen successful missions!
System Shock 1 and 2 never scared me at all, they were great 'drama's' in my book though!

Xerxe's alarm manages to bury itself into my brain at times. Couple that with the med-lab BGM and it gets a little nerve racking. Even then, sometimes I actually end up with X-com music behind the alarm.

Uplink and Deus Ex scared the shit outta me more than once.
I'm not very jumpy when it comes to traditional horror, but hell, Revelation and Icarus?
Made me piss my pants.