Eternal Darkness. Developer Silicon Knights really broke the mold when they made this one. Eternal Darkness doesn't play like any game before, breaking the "fourth-wall" of video games to scare you to your gamer core. While I've managed to play with the lights off through about the fourth chapter, I recently tried to play again, starting from the beginning. I think I made it into a handful of rooms in the mansion before I had to put it away. Eternal Darkness even starts off with a bang, thrusting you into a dream before pulling the veil back on the lead character and introducing some of the more psychological, mysterious, and unnerving elements in the game. Eternal Darkness is easily my favorite "scary" game.
Super Mario 64. You may laugh, but goddam was I terrified of Bowser. I would relegate myself to the bomb-omb battlefield for hours at a time before I faced him in the castle's main chamber. And why do the Boos scare me so badly? Even to this day, I'll avoid the haunted mansion world for as long as I possibly can. Part of my fear surrounding Super Mario 64 was how hard it was for me to wrap my hands around some of the challenges. I couldn't stand to see Mario drowning in the quicksand around the pyramid, or rolling over in the murky water.
Killer 7. Killer 7 might not be considered a scary game by some, but for me, it was the atmosphere, the visuals, the overall creepiness that had me weirded out. How is it possible to play through two discs of this stuff? Some day, I'll grow a pair and manage to get through a large chunk of it and then force myself to finish. Until then, Killer 7 will sit around in my game bag for a while.
Resident Evil 4. Luckily for me, I actually managed to get through a lot of Resident Evil 4, despite the fact that it was mostly a panicked run, my sweaty palms barely able to contain the controller in my fists. It's not scary so much as it is tense, atmospheric, and diabolically difficult. Goddam that boulder crushing me because I couldn't tap A or jam L+R fast enough.
See, I told you I don't play scary games. If I did, the list would be longer.