Thief: Deadly Shadows - Shalebridge Cradle. Enough said, and always gets mentioned first by me whenever the "scary game" thread appears. That place was a nightmare!
Other than, I haven't really played many "scary" games in recent years, so nothing in particular comes to mind. I may get slightly unnerved by certain things though. Playing Fallout 3 for example when I had wandered into the Yao Gui (sp?) tunnels for a bit of an explore. Now, I had the Animal Friend perk so was able wander about at my leisure. There was just something about the sounds those bear like creatures made though that got to me. Especially when I rounded a corner and one was right there staring me in the face. Just had to put with it until I got out, as opening fire when right in the middle of a pack of them wouldn't have gone well.