I don't think I've had a game "scare" me in the same way Aliens did my young self into nightmares for months^[and made it one of my favorite movies of all time too], but I've definitely played some very tense games that definitely had me on edge.

• Doom - Probably my first "horror" game simply by it's premise. This was made worse because there was keyboard only controls back then and I was terrible at strafing. Everything from the music^[SNES Doom E1M5 lives in my head rent free], the sound effects of the monsters, managing ammo, and the increasingly deranged levels (both layout and theme) all were layered together that had me on the edge multiple times. If my mom saw it she definitely would have banned it!
• Dark Forces - While not a horror game specifically, it does have its moments especially from its Doom heritage. I hated the third level, the sewer level, because those garbage tentacle monsters would jump up and bite your face with little warning. When I say little warning, I mean you might see, in the dark, a little tentacle eye peep out in the distance. Combined with it's dark maze-like layout and it has no humans made for a very creepy experience. I mean just look at this nonsense!

• SNES Jurassic Park 1 - It's all fun and games in this top down shooter until you enter a facility and suddenly you are playing Doom with velociraptors.

• Half-Life 1 - Definitely my first actual horror game. I've also raved about it before. If Doom laid the building blocks for a horror FPS (survival, sounds and music, levels, vibes, etc) Half-Life built a masterpiece. It was also a first that didn't have separate levels so everything felt like an unbroken survival experience where you were just trying to get out of the facility. The purely environmental storytelling likewise added to the horror survival experience. I also want to shoutout Half-Life 2's Ravenholm for bringing this vibe back.
• Aliens Versus Predator '99 - Being an Aliens fan made this a natural for me. Probably the fastest and most lethal xenomorphs in the franchise^[on the flip side, it is so satisfying playing the xenomorph (multiplayer and campaign) even if a sneeze could kill me. I would charge in to die and people would be killed with my dead body's acid blood ] made for a terrifying marine campaign. It's also scary being the marine in multiplayer too, until I get the minigun...
• F.E.A.R. First Encounter Assault Recon - Not really a kid anymore, but this should be on the list. If the Matrix and the Ring made a video game, F.E.A.R. would be that game. A GOAT FPS horror experience with top notch matrix-time gun-fu gameplay, AI that was so good there's a GDC talk about it, and a proper horror story line and atmosphere that genuinely freaked me out several times. Holds up both graphically and story even today.

Honorable mentions:

• The Library from Halo, which is mentioned elsewhere in this thread. I also had a lot of trouble with that spot when I first played it to the point I remember just running through most of it! Having played it again recently it's clear a huge chunk of the scary was the thought the Flood was infinite, because I cleared out the whole corridor on the harder difficulty without much trouble.
• Zelda Link To The Past online rumors - I believed an online rumor that the temple background music was designed to make you confused, so during my first playthrough I turned the sound off during the temples.
• Half-Life 2 and the episodes - More of a Haunted than a Horror experience, there are several times in the games that dip into horror.
• System Shock 2 - Janky but excellent atmosphere! It's tough to recommend because you can really fuck yourself over with the wrong build and there isn't a good way to know. Since the original System Shock was remade I'm holding out SS2 will get one as well.