I tend to avoid horror games, so the scariest moment in a games for me are all from the Thief games: The levels The Haunted Cathedral, Return to the Cathedral, and Maw of Chaos from Theif: The Dark Project and the level Robbing the Cradle in Thief Deadly Shadows.