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 benefit? The 10th level ability gives you the ability to duplicate a Dragonmark's power (of Least, Lesser, or Greater) of your choice 1/day. Sounds nifty huh? Guess what? Being a 10th level Cataclysm Mage means half of Dragon-kind now wants you Deader Than Dead, no save, and no possible chance to negotiate. Given the resources available to those dragons, you are screwed.
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.
To put a more specific example: Let's take Dominic D'Honaire - he's able to control anyone he wants, and has managed to control the country from the shadows and create a vast spy network. HOWEVER, if he falls in love with anyone, he can't mind control them, and she'll just find him more repulsive the more he tries to woo her until she's driven off.
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.
It does not stop there either folks. Are you a Mad Scientist? Then you are slowly driven mad by the demons giving you all those "Keen" ideas. Be a mage and yeah not only do you need to hide your magic skills or get lynched by the scared towns folks but you ALSO have to deal with demons to get your power and messing up can mean all KINDS of unpleasent effects. Just being sucessful at stopping evil plots means that sooner or later the Big Bads will notice you and decide to shut you UP via demon bull or if you have REALLY honked em off they send their personal hit man after you: Stone.
And for the extra cherry on top, it's quite possible for a character to be multiple "special characters" at once. It's quite legal to be both a Mad Scientist and a Huckster (mage) at the same time (they're called "Metal Mages"), and both Metal Mages, Mad Scientists and Hucksters can all rise from the dead as Harrowed, or become Vampires. Which means, in essence, you've got all of the original sucky blessings, and possibly a few more depending on what sort of combination you become.
Psykers in Warhammer 40,000, 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.
Similarly, the followers of Nurgle get all sorts of "gifts" from their patron, that often take the form of diseases. They prefer to see it as Cursed with Awesome, however, as because they are permanently in pain, they can't feel any other pain. So they keep fighting until you kill them.
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.
Or, if they're lucky, they die before they get that far (although, if you were on your way to being an immortal daemon prince, you were probably unlucky to have been killed before then).
There are also Thousand Sons, whose bodies turned to sand upon the acceptance of their leader's powers, forever trapping their souls within their armor. Doesn't sound so bad compared to some Chaos gifts though.
The Thousand Sons didn't turn into animated walking suits of power armor as a result of chaos mutation; Rather, the Thousand Sons were becoming so twisted and warped by chaos that they were barely human anymore. One of their leaders and powerful psykers, Ahriman, created a spell to try and halt the progress of the Thousand Sons' mutation. It backfired.
Worse yet, if Thorgar the Blooded One from Warhammer: Battle March is any indication, the ones who succeed in their transformation actually get weaker. Since the Chaos followers in question either transform or die, with no other outcomes possible, they're screwed regardless.
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.
Actually, being a male with magical potential in Bretonnia isn't that good either. All children with magical abilities are taken from their families at a very young age, only girls are ever heard of again.
Just going insane is actually pretty lucky for a 40K Psyker (and Warhammer Mages, for that matter); the reason the Imperium is so hard on psykers is because, if they're not trained properly, they get their soul eaten by a Daemon, who then borrows the body for a bit of a rampage. There are occasional references to whole worlds being destroyed because of one unprotected psyker - either because of a daemonic infestation, or because the Imperium used Exterminatus (destroying all life on the planet) to halt it.
One of the "better" fates for a 40k psyker is to be "recruited" by one of the Black Ships that patrol the Imperium looking for low-level psykers, to be taken to Terra to power the Astronomican, the great psychic navigational aid. Sure, the process devours the psyker's body and soul, but at least they're being useful.
If your talent is great enough, you can look forward to being a Primaris Psyker and using that talent to put the hurt on some of the Imperium's enemies before you more than likely die from some Warp-related incident.
Psykers border this and Cursed with Awesome. One of the potential futures for psykers taken aboard the Black Ships is either being accepted as the apprentice of an Inquisitor, or becoming one of the Grey Knights. However, both outcomes have their own suck-ish endings (namely the former has to become something every citizen of the Imperium fears, while the latter means you will die relatively nameless to the people you protect somewhere).
Regarding the Grey Knights, that is a pretty straight example. Sure, you become a seven foot tall Magic Knight wearing Powered Armor, but the process of actually becoming a Grey Knight is so mentally intense that all of your memories and original personality are burned out, so for all intents and purposes the person you were is dead.
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.
On the subject of psykers in 40K, 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.
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.
Oh, what's that, you say? Fae power as long as the changeling isn't caught? Well, that doesn't sound so bad, you say. But wait, there's more - instead of the "Morality" scale that mortals are judged on, changelings have to pay attention to their "Clarity," which is their grip on sanity. At the top of the chart, most of their powers are completely inaccessible; at the bottom, however, sevenseveral floaty sparks carpe mindmaps no pointclick. So yeah, there's that...
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...
Given that even your own magic has a tendency to go very bad, go exactly as intended, disappear at a vital moment because some passerby noticed it, or outright accidentally break the universe for a while in a given area, there is plenty of potential for the awakening to be a blessing with suck in its own right, no setting elements necessary. Mages playing in a Vampire or Werewolf campaign without the universe being out to get them are still cavemen playing with a short-circuiting plasma rifle strapped to an A-bomb, the fact that it goes right until it really, really doesn't is the trap. Well, that and the fact that you're driven temporarily severely insane every time you botch a roll...
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 as for the Demons? We don't know much, but we do know the God-Machine wants them back, one way or another. They're still useful, after all...
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 unsuable 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.
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 immuned 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.
This is a strong theme in the Charmsets of the Infernal Exalted. One of their powers, for example, lets you run forever (very fast) without tiring. The catch is that sleeping is no longer restful for you; running is. Have fun constantly dashing around to avoid exhaustion, freak.
As is befitting Infernals, its successor Charm removes the "suck" by widening the "energy from activity" criteria to "anything somewhat strenuous", meaning that to avoid exhaustion you...have a normal workday, or do something that results in an adrenaline rush (which is part of a normal workday for Exalted).
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. Machine Empathy is a particularly suck to be blessed with, as the computer immediately notices it, and execution 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 causes you to be able to perceive the astral plane, a shocking experience for most. 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.