Nightbreed (i think) was pretty scary, the bit where you have to run away fromthe guy in the graveyard, and the weird flying mata ray things. Pity the game wasn't much good though. PP hammer was not "scary" as such, but when the crazy man in the turban chased after you used to make me jump and panic a bit. Lombard RAC rally was scary at how bad it was, does that count?
edit: geeza, just read your above post, that could be PP Hammer that you were referring to, although I think it was probably a remake of another (probably better) game