Jaime: No one mentioned "I Have No Mouth, and I Must Scream" yet? Haven't played it myself, but it's apparently very popular with adventure game fans. Probably hard to get, though.
I'd say it was creepy and atmospheric, but not scary. The original short story was fantastic.
For point and click, I'd recommend "Dark Seed" - really wish GOG would bring that out and its sequel!
For other scary games - the Silent hill games (1 and 2 being my personal favourites). Amnesia, the Penumbra series. Resident Evil 2.
Back in the day, Dungeon Master and Chaos Strikes Back scared me as a kid ("Arrgh, I'm trapped in a dead end by a group of rock monsters!" type situations :D). I guess Legend of Grimrock is as close as you're going to get in GOG...