Not exactly scary, but Thief 1's level on the Cathedral was certainly tense and uneasy.
I too found System Shock 2 to be rather scary at times. I remember going through a set of rooms situated on a catwalk, looking for supplies or whatever and as I was coming out from each room I would lean out first to check if anybody was around. This was a great recipe to shit my pants as the one time I did that an enemy was behind me, and as soon as he spotted me started shouting "Kill me!". I seriously almost fell of my chair.
Scratches is an amazing adventure game that kept me on the edge as well. There are two particular moments that always spring to mind, the first one would have to be the one with the mask and then the other one the cellar/boiler room scare. Thankfully, I wasn't playing late at night as I would have been unable to sleep. Those who have played the game will surely know what I am talking about.
As a child, I remember being very scared of Half-Life and Soul Reaver's main menu of all things. For those of you that don't know/remember, the main menu in Soul Reaver had about 7 statues that would turn around and look towards the screen with demonic faces at random. Here it is just for reference:
https://www.youtube.com/watch?v=z_1U-wVU488
More recently, I played through The Lost Crown with my girlfriend which had quite a few legitimate scares. It scared the living crap out of us on a number of occasions, to the point where I think I would have been unable to finish it on my own.