There have been two moments that I can remember where I was actually scared enough by a game to actually yelp out loud. I'm sure there have been more, but these two I remember very well.
The most recent one was in Doom 3, which was a pretty scary game overall. There's one part where you're wandering around this abandoned moon base where almost every single human has been killed and you hear a woman scream out for help. "Help me! Someone, please! My baby! My baby needs help! Someone please help me!" You can hear it coming from a long, curving hallway. Naturally, I went to investigate. As I went further and further down the hallway, I could hear her continuing to sob and her baby wailing. At the end of the hallway was a final turn, leading to a locked door. As soon as I could see the locked door, the lights shut off. I switched to my flashlight, and then heard a whisper behind me, "over here!" When I spun around, I saw in my dim flashlight not a woman and her baby, but a demon clawing frantically at my face. I screamed a little. Doom 3 had plenty of startling moments, but that's the one I always remember.
That's the story I usually tell when asked about my favorite scary moment in gaming. But there's a second moment, and oddly enough, it's from the original Command and Conquer. The Brotherhood of Nod (the bad guy terrorist faction) has a base defense structure called the Obelisk of Light, a curved, towering black spire. I pretty much always play the good guys first in a game, and as I was scouting around and discovered a Nod base for the first time, I heard this low high voltage buzzing noise. A few seconds later, my speakers emitted a loud ray-gun noise as a bright red laser beam fired from the obelisk, destroying my scout vehicle in a single shot. The terror I felt at that moment from those fifty pixels and that low-fi sound effect was the most scared I had ever been of a game until Doom 3.