The only games that really terrify me, make me jump, and keep me from playing alone are the Fatal Frame games. Specifically, Fatal Frame III, the only one I never finished. The part that I couldn't handle was being stuck crawling through a tunnel with a ghost attacking me through the walls. Limited movement, my own claustrophobia, and being unable to see what direction the ghost was coming from until it was too late contributed to me being too scared to continue.

Dr. Chaos on the NES also frightened me for a long time. When you go into the first-person room exploration bits, you have a certain number of moves before a creature rushes the screen the next time you turn around, forcing you back into the hallway where you'll probably die. It always freaked me out when a skinless gorilla would come at me without warning.

I'm also scared of SSX games. I'll play them, but due to a skiing accident, I have to be in a certain mindframe to play them, or else I go into a cold sweat and have flashbacks to my injury.

There are always classics like RE2 or Silent Hill. For frights both more recent and more embarrassing, though, Condemned for the 360, at launch actually had me yell in my room and confess to my housemates when they came rushing, that I was just plain scared. The sequel had me freaking so hard in the first stage that I decided I wasn't playing it anymore. Especially since my free game time was usually late at night. You know, when you pause th game after a while to look around and make sure you didn't just ... hear something...

I played a few hours into Fatal Frame before I realized that it was a really bad idea to be playing it by myself at night. It got so bad one night that I actually left my house to go visit a friend at work just so I wouldn't have to be alone with that fucking game. I never picked it back up, and I've often been tempted to re-shrinkwrap it (but not enough that I've actually done so). I don't really remember what freaked me out so much about that game, but that's probably for the best.

Also, Half-Life 2. I'm pretty sure that poison headcrabs are the only video game monsters I've still seen when I closed my eyes. Mainly because they look like giant spindly spiders, and they make that goddamn chirping noise, which I imagined to be the sound of Satan giggling. The fact that they'd put you near death with a single hit was relatively incidental.

I've got two words for you: 

Yume Nikki(Dream Diary).

I've rarely played a game that made me so afraid to venture further. It's status as a freeware game shouldn't fool you; it's one of the most surreal and unsettling games in recent memory. I've heard many people describe it as Earthbound meets Silent Hill.  Go look up the translated version. I implore you. 

Yume Nikki is less of a game and more of an exploration of a girls dreams--the kind of dreams that make you want to send this girl to a psychologist. There's no real goal except to find these "effects" which tend to do very little. You don't actually die in the game: the worst that happens is getting stuck where you need to "wake up". Even without traditional game design elements, its atmosphere proves to be one of the darkest ever seen in gaming. 
The game's designer, Kikiyama deserves the attention of Grasshopper, as his/her style of design would fit with the developer.

The original Fatal Frame definitely gets my vote. If you think about it, what do you do with the most dangerous creatures in a horror game? You stay far away from them!

Hell Knights in Doom 3? Get a mini-gun and put some distance between you and him! The Witch in Left 4 Dead? Get a rifle and unload headshots before she can swipe at you!

Fatal Frame however basically tells you that you must let these apparitions come as close to you as possible in order to cause damage to them. Looking through a sh*tty camera lens, trying to find the ghost when . . . all of a sudden it pops up 6 inches from your face! AHHHHHH *click click* GREAT THAT DID BARELY ANY DAMAGE.

Sh*t . . . where did it go . . . k I'm gonna leave the room no . . F*CK it's back and with a broken neck GODDAMNIT DIE DIE DIE.

This process is repeated with equal effectiveness hundreds of times in the game. This is the type of game you pause to let your heart rate settle.

Fear Effect scared the hell out of me when I first played it. Sure, the graphics are terribly outdated and half of the time I was fighting a pre-rendered video of a monster. However, I still get really freaked out whenever the game forces me to enter hell.

And then there's also the part where one of the characters gets his arm chopped off. Then I literally have to play part of the game with only one arm to shoot at enemies. It's not a pleasant experience.

I still get the shivers from that game. I feel silly when my character is rolling all the time to dodge bullets, but I'll never forget that game. Ever.

I'm glad I'm not the only one who is completely creeped out of my skin by Fatal Frame. My childhood was drenched in the blood-gushing movies of Freddy, Jason, Michael and the ilk, but that game ... ~chills~

I still remember the first house I entered, armed with only that camera. You know something is there, but you can't see it. You try to find it through that camera, and then — click — GAAAH! And even if you wanted to run, the game doesn't let you go any faster than a zombie walk. Seriously, if I had a sister that went missing and that was the only way to find her, I would no longer have a sister.

Fatal Frame even beats out the zombie mutant dogs in Resident Evil, which still unnerve me.

The game called LSD on the PS1 really messed with me. I don't know why, but I always get a feeling of dread playing it. I guess it was a dream sim or something, but all its ever done was creep me out.