System Shock 1 and 2 have to be the the most scary ones. I still have nightmares about beta groove in System Shock. And I remember my girlfriend sneaking up on me while I was playing SS2, and then lying her arms around my neck. I got so scared that it actually hurt. My nerves flew out of the window every time I played those games...

I also think the Silent Hill games are damn scary. And I actually did fall of my chair when the dogs jumped through the window at the start of Resident Evil. That was the last time that I've tilted the chair backwards while playing a game... But other than that, I've never found the Resident Evil games to be scary.