My scariest game was Zelda Majora's Mask.
I was alone in the house and it was dark, and I was in the creepy well. I had just finished defeating many enemies in a particularly scary room and was in a hall. I figured the game was giving me a break and I was so happy as I walked slowly to the other side. Then all of a sudden A GIANT BLACK HAND drops down and picks me up and takes me to the start of the level. I nearly had a heart attack and I couldn't play again for months.
When I finally gathered the courage (and the strategy guide) to finish the level, I made it through and I rejoiced at finding the ladder that takes you to the castle. Then all of a sudden this loud trumpet music starts up and frightens the heck out of me.
Majora's Mask has some creepy stuff in it, like Redeads, Wallmasters, the whole fourth area and the Happy Mask guy (check out his expression when he finds out you lost his mask).