I think it's scary in the sense that it really leaves you feeling helpless in the face of the necromorphs' relentlessness and the way that the entire crew seems to have succumbed to...whatever happened. Obviously Isaac can and does survive if you finish the game, but it does a good job of making you feel desperate and aware that a gruesome death at the hands of something you can't possibly understand may be right around the corner. And I think it did make me "jump" in my seat a few times, at least the first time I played it.
Well for me, a big part of not getting scared by games is knowing that there is always going to be a way to beat it.
That's why the lack of a resource makes things so much more tense for me. I still know there is a way of beating the game, but I know it's tied to the resource that I'm about to run out of. I'm 1 step away from being truly helpless.
Of course in that case you can end up in a scenario where the player can't progress because they've run out of the means to defend themselves(usually bullets), and developers generally try to avoid the player having to lose significant amounts of progress these days so most of the games overload you with more ammo than you generally need.
Another thing that helps is not letting your player get a good glimpse of the monster. Our imaginations will naturally tailor what we think it might look like to be far more scary to us personally than what a game developer can do. It's not always about knowing that you might die but rather about what your imagination dreams up that might jump out at you.
I think Dead Space did a good job at being very atmospheric and it even startled me a few times, but it didn't actually scare me. I did still enjoy it despite that.