Hmm... I haven't been really scared of any game since I played Zombies ate my Neighbours when I was a kid. The game itself wasn't at all scary, but the way the humans screamed if they where killed by zombies freaked me out, so I had to quit playing it.
The missions in Thief where there were a lot of zombies was also a bit scary, but in a positive way. I remember I used to play it with a friend, were we would sit for hours in almost complete silence, only wispering to each other because we were afraid the guards whould here us if we made to much noise
Resident Evil 2 was never scary, it only had a very good atmosphere which I enjoyed a lot. I never played it during the day, I had to sit in a dark room to get the right feeling