It is no secret that I have a preference for a certain play-style that I fondly refer to as "dungeon horror". I describe it as "dungeon horror", which is a term I borrowed from some forgotten source, because it succinctly describes the mood I desire to create at my table and my preference for running sessions in the underground adventure environment. Drawing inspiration from a life-long time of reading tales of adventure and mystery involving lost civilizations, forgotten tombs and haunted caverns, everything from Creepy and Weird Tales to the work of Professor Tolkien - yes, I see The Shadow as a scary thing, despite many efforts of the cinema to make it otherwise!
Superheroes are indeed fun to play - in the right setting. The ability to meet every challenge head-on with reasonable expectation of success, empowers and thrills our illusion of control and sense of mastery, even when it occurs in a fictional game setting. Such is the power of "story" to seem real. Superheroes can have vulnerabilities, but they are seldom "fatal" and they can be expected to prevail in the end. Superheroes nearly always "save the day".
System matters! It is a phrase one sometimes hears while hanging around this hobby. If you have experience with any so-called generic system for tabletop role-playing, you may have noticed that they give the person setting up the game (referee/GM) some to a lot of ability to control or set the variable power level for newly created characters. For example, in the popular game GURPS a starting character of 100 points is much more normal human like and therefore more vulnerable than a character built using 500 points, or even 250 points. The more points one has in GURPS to spend on character creation, the greater the power of starting character abilities, skills and powers.
Horror role-playing games are not about smashing things. They are about survival. They are about smart play, solving mysteries and thwarting the evil machinations of sinister, shadowy forces - often by merely exposing them. From such modest level of accomplishment is "satisfaction" derived while playing and succeeding at a horror themed game.
Immersion is a by-product of role-play - or for some, I suppose the principal goal in playing. Immersion in a "scary" environment requires a degree of vulnerability - some tension and anxiety on the part of the player helps. This is achieved in the role-play setting through the character's vulnerability, not that of the players, who ideally sit comfortably in a very safe place throughout the game. Risking their character is what provides the "fear" in role-playing. Imagining the consequences of failure in terms of the fictional story can add also to the tension. The presence of superheroic character abilities seems to be very much at odds with this experience.
So how should we define "competence" in terms of character ability in a horror setting? I frequently compare the character's skills and abilities to those of an "average" human. Many, if not most, fantasy characters have skills and abilities "above average" in one area or another. Masterful fighting skills, the ability to cast divine or arcane magics, pick pockets, jimmy locks, navigate computer novel systems or turn undead creatures sets the player character well above the abilities of most "normal" humans, yet do not necessarily imply an ability to brush aside all evil threats. Being competent without being super competent is of course a judgement call. I would like to see the players challenged, but not feeling helpless. I would like them to believe that careful and smart play can get them through, even if relying on their character sheet can't.
So this is why I prefer a more believably human level of power in my play. To achieve a heroic result while playing a person who could fail, who is vulnerable and might not survive the adventure. To succeed without having the game "stacked in favor of the characters" - that is what I seek.