Heres some good ones that havnt yet been mentioned:
If you like oldschool adcenture games, (free legally) try:
5 days a stranger, 7 days a skeptic
If you are prepared to get horrified try System shock 2, it blows any other game away in terms of scarieness.
Resident evil 4 was also very fun, although not too scary.
Fear and doom 3 have already been mentioned, FEAR is more scary then doom 3 and both are fun. System shock 2 is much more scary then either of these.