Resident Evil 3: Nemesis scared me when I played it - just knowing that Nemesis could be anywhere around you at any time, following you, waiting in the dark. That was the most tense experience I've had playing a videogame.

For me the most early memory of me crapping my pants from a game has to be Alien vs. Predator on the Jaguar when i was a bairn bloody facehuggers got me every time, then it had to be resident evil one with the dogs and thridly eternal darkness for making me paranoid when entering every room

I just rented Homecoming (it's turning into a buy very quickly), and I'd say that is some very scary shit.

I don't think that I was very scared or anything, but when StarCraft came out, I played it so much that I would end up dreaming about it. And of course I was a marine fighting the zerg. It seemed real in my dreams.

Dungeon Master on the Amiga circa 1989...
Got me right from the start; doen steps to the first level, you hear padding footsteps, come to a gated cell and there's a mummy that suddenly shreiks at you!
Also, if had used up your torches to see your way and your magic casters were dead, you were forced to virtually scrabble your way in the dark virtually blind, trying to find the nearest restorative fountain to resurrect them. But that could mean a journey back up a level...

When I was pretty young, I played the original Dark Forces and I could not get past the sewer planet level because of the damn dianogas. I'd see an eye pop up and I just kinda stood there, wondering what I saw until I heard a noise, turned around, and saw its mouth right in my face.
More recently, F.E.A.R. and Half-Life 2 creeped me out in about the same way. I avoided dark rooms as much as possible in both games.

Uplink was scary, but in a much different way than most horror games. The thought of being traced after failing at hacking was too much to bear when I played it. I remember constantly looking over my shoulder, waiting for some SWAT team to come crashing through my window.

my first encounter with a Skaarj in Unreal... the lights swithcinh off in that corridor, the sound. everything was perfect to freak the player out :)
otherwise, the 7th Guest was kinda creepy too =)

Oh yeah, I remember that Unreal moment. Scared the crud outta me too.

In terms of recent games, Penumbra: Black Plague had me on the edge of unleashing a catastrophic volume crap in my pants.

I agree on the Unreal scene when the first Skaarj attacks. When the lights switched off and some bar trapped you in the darkening corridor, the Skaarj growling in the shadows.. excellent moment.
In terms of a general feeling of uneasiness I vote for Sanitarium (I think it was already mentioned here). The atmosphere of each level, and the game forcing the player to figure out the sick logic behind each setting was just awesome and chilling to the bones.
The game that made me jump the highest was Aliens versus Predator, the first person shooter which initally didn't even allow the player to save. As a colonial marine the fast and really deadly aliens scared the eyes out of my sockets .. and after the first few facehugger encounters I had nightmares for weeks. Good times :)
(Sadly the other two campaigns didn't keep up. The Alien campaign was great fun I think, but the predator was just boring. He's invisible, he can heal himself, he has night & thermal vision and his gun has auto aiming .. the only thing missing was a personal teleporter o_O)

I'm not sure I saw it mentioned but the Thief series were always pretty excellent games for tension and the Orphanage in Deadly Shadows was really well paced and had me jump a few times.
A friend of mine was scared witless by Doom 3, I nearly lost my mouse when he launched it skyward after a teleporting demon leapt in behind him. Made the rest of us laugh out loud though :)

The 11th Hour was a bit creepy but at the same time had a bit of carnival funhouse mixed in. Difficult to describe to someone who's never played it... perhaps the best way to put it is the designers added an Addams Family touch.
AMBER: Journeys Beyond wasn't really a horror game, but succeeded well in the tension department. To be honest I've never played another game that managed to make me feel that "I" was trapped and -had- to solve the puzzles to get out.

Call of Cthulu: Dark Corners of the Earth...
It still haunts me... When few people start to chase after you, you have no weapons and have to figure out how to get out of the place... damn that was scary... and it was not just the momentary *boo* scary but kept creeping through the whole chase(few minutes+countless loads when figuring the route for escape) and the game's blurry vision just made the feeling more real and horrifying...
on the side note: Usually when watching horror movies or playing horror games I don't have much of reactions on the spooky stuff...