I'd have to search long and hard in the memory banks, but It Came From The Desert quickly comes to mind. Very well done, great atmosphere, creepy and excellent music.
Out of This World & Flashback were pretty intense from a gamers point of view. Weird anyway. None of these games "scared" the crap out of me or anything (was already too old by that point), but they have their moments. Guess the last game I felt was executed really well "scary" wise would have been the Atari Jaguar's Alien Vs. Predator.