Paladins are sometimes depicted as being such Slaves To PR as to have little free will. Thus, they must do the most obvious "Lawful Good" action to any situation, even if the end result will obviously be bad, or else lose their powers or worse. Many have called this interpretation "Lawful Stupid."
9/10 of every template in the game (meaning you have some special heritage or similar background that grants you special ability). Yes, you are born 'special'. Yes, you get extra powers your Muggle compatriots don't get, on top of your normal race and class abilities. However, due to Competitive Balance, you get a Level Adjustment that means you are actually a lower level than your fellow party members, which means they have access to cooler toys and powers than you do. In addition to this, comes the role-playing aspects. The remaining 10% of the templates tend to be Game Breakers, as the advantages vastly outweigh the loss of a level (unless you're a magic-user).
The Cataclysm Mage prestige class from Eberron. One of the class features is a self-related prophecy. Said prophecy has a 1/4 chance of being fatal. It is possible to survive all but one of those prophecies (the exception being one that requires you to die). You get a special benefit for fulfilling these prophecies. At the DM's discretion, the prophecies have to be fulfilled in order for you to advance in the prestige class.
The 4e update tried to discard this trope, removing the "Must be Lawful Good" from Paladins, removing the capacity for classes to have their powers stripped from them altogether (no anti-magic fields or divine rebukes, for example), removing racial templates by trying to convert them to new races or just to generally less-powerful "bloodlines" (half-dragons being replaced by Dragonborn and the Vampire template being replaced by the Dhampyr bloodline and Vampire class, for example), and simultaneously lowering the power of wizards (and raising the power of other classes) while redesigning the way powers work so a wizard can never really run out of spells. Fans are divided.
Commit an evil act in the Ravenloft setting, and you may be subject to a Powers check. Fail one, and the Dark Powers "reward" you with a nice perk to your capabilities, along with a trivial penalty such as a small, but creepy change in appearance. Fail another, and you get a slightly smaller perk and a marginally more harsh penalty. Keep it up long enough, and you'll be getting slammed by heavy defects with negligible benefits... oh, and you'll become an NPC darklord, trapped forever in a domain that makes you Extra-Blessed With Megasuck.
The Harrowed in Deadlands fit this to a proverbial "T". Not many people get a second shot at life, even if it is in the "crawling your way out of the grave" sort of way. Being harder to kill (again) and manifesting some of the game's strongest powers sounds great...until your personal demon takes over, often resulting in deaths among your posse. The only player character type that has more power has even moresuck: vampires.
Psykers, especially amongst humans. The fate of the overwhelming majority of psykers in the Imperium is to die at their hands of their fellow humans, who hate and despise them. If they're lucky, said death will just be a bullet through the brainpan or being burned at the stake. If they aren't caught and killed (or controlled by the Imperium, not a nice fate either), they usually go insane. Or worse. Given the fact that Psykers gain their powers because they have a gate to the realms of soul devouring demons and endless, sanity shredding horrors in their brains, they're practically the living embodiment of this trope.
Chaos gifts in general are like this. They can grant you great power, but mutate your form with each upgrade. The careers of Chaos followers ultimately end at one of two destinations: as a Daemon Prince that transcends mortality, or as a gibbering Chaos Spawn, whose body and mind has been warped beyond recognition.
And if you think the fate of a psyker is bad, try being a blank. Blanks are people who have no warp presence whatsoever, meaning that Warp-energy cannot affect them, rendering them immune to psyker powers and daemonic possession. It also means that they are effectively soulless. This, in turn, means that ordinary humans are repulsed to the point of hostility by them, meaning they can't hold a job, can't have sex, unless they are willing and pay someone to dope themselves out of their skull and do it, can't have a family because no-one can stand to be around them, and if they walk into the same room as a psyker, the psyker will immediately double over in pain... Oh, and if one is caught by the Necrons, they immediately rebuild the blank in question into a biomechanical super-soldier. Being burned at the stake by your family suddenly doesn't seem so bad. Oh, and if the Imperium finds out about you, they'll put you in the Inquisition, and you can spend the rest of your (probably brief) life escorting Inquisitors on the most dangerous missions, where you can almost certainly count on encountering demons, witches, and other nightmarish monstrosities. Unless your name is Jurgen, in which case you get to be the assistant to one of the most legendary heroes of the Imperium, with all the potential perks that would involve.
The Eldar were the most psychically gifted species in the galaxy, which helped bring about the downfall of their civilization when their decadence and perversion so disturbed the Immaterium as to give rise to a Chaos god of, well, decadence and perversion. Then they got it again when Slaanesh, in addition to destroying their civilization, driving them to the brink of extinction, and also killed most of their gods, including the king of the Eldar pantheon, Asuryan. Asuryan, in his last act, distributed his power among the surviving Eldar, vastly increasing their already long lifespans and their already prodigious psychic powers, but also suppressing their fertility. As a result, they have been unable to come back from the brink of extinction, even after ten thousand years.
The Kroot are a species with Cannibalism Super Power, allowing them to mutate themselves and gain useful traits by consuming the flesh of other creatures. Thing is, if they eat too much of a particular kind of meat, they end up devolving into a new creature, usually one that loses its intelligence and its mutative ability. To put this in perspective, every animal on Pechor, the Kroot homeworld? Is actually a Kroot substrain that has become trapped as a mindless beast due to overindulgence in a certain kind of meat. Krootoxen are lumbering, gorilla-like herbivores, while Kroothounds are little more than dogs, for example.
The Tau have almost no warp presence, much like Blanks, but also do not creep people the hell out. It also means they do not fall prey to Chaos (at least not as easily as other races) because they flat out can't hear the whispers of the gods. The downside is that they have absolutely no concept of the warp at all and are unable to utilize warp-based technologies, such as Warp Jump on starships. They can get close and "skim" on the rim of the Warp, but this means that they're basically playing hopscotch across the galaxy while everyone else are in racecars.
The Astronomican allows for plotting of the warp, which allows for safer routes for imperial forces that minimizes daemon attacks and can adhere to some semblance of a schedule. It's why the Imperium can still function despite the galaxy-wide empire. However, it's also reeeeeeeally bright in the warp, which is implied to be the reason the Tyranids (a highly psychic-sensitive race) are invading the galaxy.
Mages from Warhammer don't get off much better, the people generally fear and hate them but at least the government isn't out to get them. This is really only the case in the Empire though, in most other regions mages are honored and respected; especially the Elves and Lizardmen.
Vampires have great power, but they're also locked in an eternal war with their Superpowered Evil Side, which they almost all lose eventually. This along with the blood-drinking and the sun burning you to ash. Oh, and boredom. Lots and lots of boredom.. And the loneliness that comes with being driven to kill weaker and equally powerful vampires and avoiding stronger ones who are driven to kill you.
Werewolves are brutally powerful... and locked in a war with the spirit world and half of their own race. Plus the Unstoppable Rage that, even when successfully directed away from innocents, causes a subtle aura that creeps out/drives off Muggles... and the consequences of mating with their own kind.
Prometheans defineBlessed with Suck - despite their supernatural strength, their mere presence turns the world miserable and drives humans insane, they're hunted by Pandorans (twisted entities made when an attempt to create a Promethean fails) that long for their flesh, and the only way out is To Become Human.
Changelings in the nWOD are the only group that have it worse - magical abilities are a cold gift when you can never be sure The Fair Folk won't show up one day and drag you back into Arcadia to serve the wretched life of a slave.
Being a Mage isn't, in and of itself, a bad thing. The world the Awakening opens up, however, is a different matter. The world you knew was a Lie, created by dark gods. Its rulers want the world's magic all to themselves. There's a gaping hole in the universe that's inimical to all existence. And then there are the Banishers, mages who blame magic for everything that's wrong with the world, and seek to eliminate it whenever they can...
The Hunters, oddly enough, might be considered to have it easiest; most of them are ordinary mortals without any superpowers. The catch? They get to go up against all of the above supernatural creatures... without any superpowers. Then there's the toll hunting can take on an ordinary life. Oh, and there's the risk of turning into the antagonist in a Slasher Movie, too. Those few Hunters who do get superpowers often find things don't get any easier...
In addition, Hunters can be used as antagonists to further outline the Blessed With Suck side of being a supernatural.
Mechanically, mortal skills tend to be just as powerful and significantly more generally applicable than anything but certain Vampire disciplines and the Geist Boneyards. A hunter going up against a werewolf, for instance, is with a bit of preparation not much more outmatched than if he were going against a mortal kung-fu master, given that the silver thing is common knowledge.
Now it's a matter of seeing how the Geists and Sin-Eaters get screwed...
Sin-Eaters are unkillable. They don't live forever, though. However, any time they're killed their Geist can bring them back to life... by killing a random person, whose final moments are the first thing the Sin-Eater sees when they come back... Oh, and if they die enough, their soul is shredded, and if their Geist doesn't just let them die, it's driven insane by what's left of them and becomes even more murderous... Yeah.
Growing in power means a Sin-Eater has to start taking up some affectations of the dead; they need Anchors to hold onto their power - as any Mage will tell you, having to keep items with a sympathetic connection to yourself is never a helpful thing - and as their power grows, they need to spend more and more time in the Underworld; by Psyche 10, they'll be spending half their time in the Underworld. The Kerberoi live in the Underworld. Do the math.
That said, most of the Sin-Eaters are going to try and enjoy themselves, given that what they do helps people (dead people, but people nonetheless), they are guaranteed to live a long life and die contented (meaning they won't leave behind a ghost themselves) and the alternative to being a Sin-Eater is being, well, dead.
Mummies are, to all intents and purposes, immortal. They don't actually get to enjoy it for very long, though; they're usually awoken for a purpose, and when that purpose is completed, they return to their sleep. In addition, when they rise from the tomb, they're at the very peak of their power, but their power steadily ebbs away over time, until it finally runs out, sending them back into slumber. They can be free of this perpetual cycle... if they're willing to accept the loss of most of their power in the process.
And the demons? Having Fallen, they now have individuality and freedom... but the God-Machine wants them either dead, or brought back, for its own inscrutable ends, and it has a very long reach. Unfortunately, demons need to stick close to G-M facilities in order to replenish their supernatural energies. Say what you will about being a changeling, but at least you don't have to stay near your former master's domain to maintain your supernatural powers.
In addition, demons also have perfect control over how they express themselves, and over the truth content of what they say (they can say something objectively true and have it read as a lie, or have a lie read as objectively true). As a result, demons can never completely trust their fellows, because they can never really be sure of their true intentions.
The game is actually considerably more horrific if your character was a sane scientist, once upon a time. See, Inspiration is a form of madness, and makes it impossible to do actual research; were you working on a cure for cancer, prior to your Breakthrough? Good news! You can make it now! But it's completely unusable by normal people because your formulas are actually the nonsensical scribblings of a lunatic. You'll also never be able communicate your ideas to other scientists again because of Jabir. Kiss any possibility for respect in academia goodbye. At best, your former peers will think you're a tragic burn-out, and face it; they're not far off.
Even better: no matter how powerful you become, no matter what great accomplishments you have to your name, no matter whose ass you kick, it will not matter in the end. The timeline "ends" with the heat death of the universe, with nothing remaining but the Cold Ones. And that's just in the long run. In the short run, your creations are often more fragile than mundane fare unless you go out of your way to toughen them up, everything you make has some small imperfection, and the only way to get around any of it is more power. There are countless ways things can go wrong, and there are countless waysthose can be rendered meaningless. It's the World of Darkness; it is not a nice place.
Dragons are intelligent, fire-breathing, flying reptiles gifted with supernatural powers, immortality, and a natural gift for leadership, along with a human form should they want to walk among mortals. However, their immortality comes from their Heart, which they removed from their body and use as a Soul Jar; should anything happen to it, they will instantly die, and you can be damn sure people will try to get their hands on it, either to destroy it or to devour it in order to become dragons themselves; because of this, Dragons usually become paranoid, living in fear that someone might find their Heart and instantly put an end to their immortal life. Because they lost the ability to have draconic children in the Deal with the Devil they made to become immortal, the only way to create new dragons is by having a mortal devour an old one's Heart, so their species no longer multiplies and is gradually dying. They also need to be worshipped or obeyed by mortals to survive, otherwise they will fossilize. And that's not even getting to the Deep Ones who are out to trick them into giving up more organs through deals...
Leviathans are Eldritch Abominations with enough power to destroy entire cities. They also have a naturally inhuman mindset, instincts urging them to consider themselves gods, and Mind Rape people around them whether they want it or not. Some of the people they drive insane end up becoming Ahab, obsessed with finding and slaying them. Despite all their powers, Leviathans are not invincible, and can actually be hurt or killed by mortals with enough firepower, which is very likely to happen considering the ravage they cause. Since their species exists only in low number, they usually are utterly without guidance, and there is en entire society of monster hunters out to get them for more than legitimate reasons.
Malkavians have the ability to prophetize and mind-whammy others, but they're all insane - every last one.
Nosferatu are masters of stealth, but are ugly as sin and can never go among humans without various tricks.
Ventrue often have good connections, charismatic, etc. But have to drink only a specific type of blood so they can't feed on animals in an emergency, and depending on what they feed on they will have to do quite a bit of planning to keep up their supplies.
Toreador are charismatic, utterly charming artists who can blend into society but suffer Creative Sterility, their artwork while technically great comes off as soulless - they also get sidetracked by pretty things.
Ravnos are masters of subtlety and deception (along with a few powers of the Romani), but they are keyed to a specific vice, which they must pursue whenever possible.
In Werewolf: The Apocalypse, the Gaia Garou can step into the spirit world, use mystical gifts, regenerate damage, and assume a clawed, musclebound, eight foot tall battle form at will. However, they're prone to enraged frenzies, and if these occur too often, they can succumb to "Thrall of the Wyrm" and commit atrocities. Their battle form induces delirium in normal humans, so they must hide their true nature from society. Finally, they've been thrown into a nigh-hopeless cosmic war against one of the primal forces of the universe.
The Black Spiral Dancers (Garou who serve the Wyrm) have all the strengths of the Gaia Garou and a powerful cosmic benefactor, but their mutations and insanity are considerable setbacks.
Fomori (humans, animals, and shape-changers possessed by banes) are immune to the delirium and have a vast array of powers. Unfortunately, their bodies and minds are deformed from bane possession. To boot, they slowly lose their free will until the banes have reduced them to mindless puppets.
In the world of Paranoia, this is used on a number of levels. Firstly, due to problems in the cloning vats everyone in Alpha Complex is a mutant, with powers ranging from super-strength and the like, to the truly sucky like having a perpetually runny nose. However, because the crazed computer that runs the complex refuses to accept that its methods cause mutations, being a mutant means you're assumed to be a spy and are subject to immediate termination from the computer or (more likely) your teammates (who, as mentioned are also mutants), if ever someone notices that you are using your power. You can register your mutation (Friend Computer is merciful to those whose genetic templates were damaged by Commie Mutant Traitor sabotage), preventing you from getting shot if your power is noticed, but then you're subject to every "treatment" R&D thinks might apply, with the side effects thereof. Machine Empathy is a particularly suck to be blessed with, as the computer immediately notices it, and execution - along with permanent erasure of your genetic template - is immediate. Finally, for every mission in the game, you are assigned equipment to use including futuristic weapons such as tangler guns and plasma rifles, but also crappy things such as Heisenberg uncertainty projectors, personal force shields working on a fusion reactor kept stable by a hand crank, robots with obnoxious personalities, and small useless boxes with loads of different buttons, dials and LEDs. These items are usually very valuable, and financial responsibility falls on the clone the equipment is assigned to. Characters are frequently denied access to documentation for the devices with the explanation that the character is not of a high enough security clearance to view the manual. The purest-essence of Blessed with Suck comes when the team is given a mutant-detecting device... since everyone is a mutant, the only thing that results is non-stop beeping and a heightened chance of being shot.
Being a Shadowrun cyberzombie does present some advantages. Because Cybernetics Eat Your Soul, you can only get so much cyberware before you're clinically dead, but with some advanced science and powerful magic, your soul can be bound to your body long after you're dead, and now you're magic-resistant and nigh-invulnerable to boot. But wait. This is a procedure that involves you dying. If you can get past that, then you'll eventually have to face the fact that no matter what you do, your body, mind and soul intrinsically know that something is wrong, and all the fancy medication you take can only stop your body from failing and you going mad for so long. Additionally, the magic used makes you a walking beacon to every spirit in the Astral Plane, and since they see you as a giant black spot, they probably don't like you... but you can't see a damn thing in there. As well as your spirit going insane in your cursed husk of a body, the procedure is very expensive. (As in, "some ruthless megacorp now owns you for the rest of your short life" expensive.)
Most scenarios of In Nomine Satanis / Magna Veritas take the form of discreet, undercover investigations amongst Muggles, and the Masquerade is sacrosanct (heh). So when one of your possible "powers" is "Incarnation in the body of a famous CEO/TV anchor/Rock star/Politician", well... Have fun trying to explain this paparazzi shot of you shooting holy lightning out of your eyeballs is really stage effects sans stage, or something. Or infiltrating the Big Bad's lair with groupies in tow.
Becoming almost any kind of magician in Unknown Armies requires developing obsessions and taboos that make it near-impossible to have anything resembling a normal life. How serious this gets depends on what specific path you follow. For example, Plutomancers gain magic by acquiring cold, hard cash - then have to live like paupers because spending it nukes their powers. Personamancers master identity and masks by destroying their own sense of self. Dipsomancers find power in booze and cirrhosis - and have no power unless they're drunk. Pornomancers enslave their sexuality to a porn star who seemed to ascend to a higher plane. These are some of the saner Adepts.
Being a Dhampir in Unhallowed Metropolis initially sounds like a case of Cursed with Awesome: you age slowly, if at all; you can innately sense the undead; you can more easily seduce or intimidate others; you have supernormal eyesight and night vision; you're immune to vampiric mind control; you can heal wounds or strengthen yourself by drinking human blood; and your existence between life and (un)death renders you immune to all diseases including the Plague that turns people into animates as well as the London smogs, on top of making you invisible to an animate's Prey Sense. Then you get to the downsides: You're emotionally unstable, you have trouble not near-mindlessly attacking vampires the moment you become aware of their presence (and the Undead Sense means you don't even have to see them first), and when you die you'll turn into a vampire — with only a 1 in 10 chance of being a sentient one rather than feral.
Call of Cthulhu. One old issue of Wizard Magazine stated that Call of Cthulhu is the only game in which the player with the fastest speed lives the longest, and the only game in which no one wants the magic item. It doesn't help the original game was a ticking countdown from sanity to insanity: your character will go insane. It's just a matter of how fast. Of course, your character might not go insane. They have a good chance of dying before they hit that point. Needless to say, ending a Call of Cthulhu game with a living and sane character is unlikely at best.
A canonical Freedom City example is the historical superhuman abolitionist Steven Mullray, aka Pathfinder, whose Super Senses bombarded him with a constant barrage of stimulation so intense and unceasing he was in perpetual pain, others doubted his sanity, and they just kept getting stronger. After the Civil War was over, he ended up leaping to his death from his own roof during a violent windstorm, as it drove him totally mad.
Rifts has Cosmo Knights, mortals who were Touched by Vorlons and turned into cosmic crusaders powerful enough to go toe to toe with starships. The drawback? They have to adhere to a very rigid code of ethics, which is overseen by a nigh omniscient entity who will not hesitate to strip the Cosmo Knight of their powers on the first offense.