Its hard to make a scary game, as whats scary for one person isnt for another! But must agree with comments about Condemned, not so much scary as feeling uneasy, especially the manikin factory. Other games that i found scary/uneasy
Resident Evil 4 (the Regenerators)
Silent Hill (School Level)
Haunting Ground
Fatel Frame 3
a game so hardcore it kill you for good when you don't eject quickly enough from your mech...
that's gotta give a true sense of danger when you are frantically restarting the engin after a well placed hit from the ennemy...