Doom 3 is pretty scarey - it got to me a few times. My friend refuses to play it because it makes me so uncomfortable. Manhunt is scarey, as is System Shock 2, a wonderful game that is destroyed by its flawed ending. Parts of Thief 2 are scarey too, as are parts of Painkiller.
On Amiga, my scarey games are Waxworks, Personal Nightmare, Ooze, and the Elvira adventures.
My god, this thread brings back the memories.