I didnt find bioshock scary... atmospheric yes, scary no. No bits in Bioshock really made me jump, most of it was shock due to the mass of blood and corpses found in the crazy surgeons medical pavillion which probably was the most chilling part of the game.
System shock 2 proved atmosphere and frights, but the game that i completed that really scared me (I didnt have shit stains it was a shit Tsunami every time i played), was Condemned criminal origins. It was like playing a role in Se7en, that awesome film with Brad Pitt and Morgan Freeman. A psycho killer on the loose, a strange and dark consipiracy and lack of light proved Condemned to be one of the most scary games i have played yet, its like Penumbra but with more melee, though one mission was really good in Condemned which was the back alley part which was perfectly lighted and was basically a suvival brawl session.
Also silent hill 2 had a powerful effect on men, i didnt even play the first one purely for experiencing the first five minutes and already i was shitting myself, though i may try that again. Silent hill 2 was great for atmosphere, everything looked so dark and grainy, and you could here sounds in the background, the banging of metal and what not... its moments like this i wish it was more of a zombie situation.