The ability to turn into the slayer works like this in Baldurs Gate 2 Shadows Of Amn, where the Player Character can transform to gain immense power and defense, but at the cost of losing control of themselves, as well as losing -2 reputation each time they do it.
BioForge: Congratulations, Dr. Mastaba's patient, you're a badass cyborg, but you're also a deformed freak (and can be controlled by anyone with the right remote control).
The Plasmids in original BioShock games. You get awesome superpowers from them, such as telekinesis and the ability to control lightning. However, you eventually get extremely disfigured from them, and they damage your mental state to such an extent that you turn into a psychotic, screaming killer. They are also very addictive, leaving people slaughtering each other for another dose.
Bloodline Champions has the Glutton bloodline, who underwent a ritual for their (very small) people when they were being horribly beaten in a war and forced into caves against a large group of bandits. This makes them grow much stronger and larger than the rest of their people as well as giving them earth magic abilities... but made them perpetually hungry.
Ryu from Breath of Fire: Dragon Quarter has the incredibly destructive dragon abilities - which, over time, cause him to lose control and die. Better still? The counter still ticks when the powers aren't in use; it just goes faster (sometimes abysmally so) when they are. And the icing on the cake: this counter can't be reversed. Ever.
John Morris of Castlevania: Bloodlines is somehow related to the Belmonts, and as a result, is able to use their Vampire Killer whip. The catch? It drains his life energy, to the point where sometime after Bloodlines and before Portrait of Ruin (the direct sequel to Bloodlines), he dies.
Shanoa, the protagonist of Castlevania: Order of Ecclesia, has this in spades. Her ability to command Glyphs gives her incredible power, including the ability to command the Dominus glyphs that each contain a piece of Dracula's power, thus giving her the ability to destroy Dracula. (Like he's not used to that by now...) Sweet, huh? Unfortunately, the ritual that GAVE her the ability to command those glyphs also took away most of her memories and emotions. The ritual to destroy Dracula, which involves using the Dominus Union, instantly drains the user's soul.... Also, while Albus's interference is implied initially to have been the cause of the memory loss, it's later implied that the ritual involving the Dominus glyphs was what caused it.
Alyssa from Clock Tower 3 could be considered this. She has a powerful bow that only works when she frees a certain spirit, is repeatedly dragged into other dimensions and chased by supernatural monsters, and is the target of her insane grandfather who wants to drink her blood so he can live forever.
In Deus Ex, Gunther Hermann is a man who got more or less all of his limbs replaced with cybernetic augmentations that make him stronger, smarter and faster than most other human beings. The kick is that by the time the events of the first Deus Ex game occur, Gunther's augs are already outdated and completely outshined by JC Denton's hardly visible, superior augs. Not to mention the kill switch that allows anyone to off the poor Gunther by simply uttering the words "Laputan Machine".
Deus Ex: Human Revolution has another, even bigger suck that goes with mechanical augs : the body will simply not tolerate bits of metal being grafted into its flesh and augs cause coagulation issues, rejection of the mechanical parts and deformities. The only way to avoid these symptoms is to take an insanely expensive drug that temporarily improves tolerance of the augs, forcing any augmented individual into ingesting a lifetime supply of meds to keep their awesome cyber-limbs in place. The main character doesn't have this problem thanks to gene therapy experiments performed on him when he was an infant.
And the inventor of the augs himself was blessed with a mind able to come up with world-changing tech, but has to live with a body so intolerant to it that he cannot even use augmentations, medication or not. It gets so frustrating for him that he ends up despising his own creation and tries to erase it from existence by turning every augmented individual into a bloodthirsty maniac.
The Mages of Dragon Age are capable of many impressive feats, including shapeshifting and healing. They are also constantly under the threat of attracting Demons to the world, or worse, being possessed by one and turned into a mindless Abomination. Oh, and the local religion loves turning them into scapegoats... Not all mage possessing entities are malevolent, like a Spirit of Faith who saved the kind old woman Wynn, keeps her alive with her will intact, and helps her to heal people. Yet despite this possibility, benevolent spirits can turn into demons afterwords, like how Anders inadvertently transformed Justice into Vengeance.
The Templars fare not much better. True, they are capable of cleansing the area from the demonic influence by sheer power of their will, but taking hefty doses of lyrium in order to do so, they end with an addiction that drives them insane.
To make it worse, Templars are awful at their jobs. In Origins, they only exist to be Worfed by something magical, regardless of how weak the magic actually is.
Feynriel in the sequel has it even worse than other mages. He is a somniari — a mage with the power to enter and shape the Fade at will without an external power source such as lyrium or blood. This makes him an even tastier target to demons than normal mages and he has suffered horrible nightmares his entire life as demons assault his mind in the Fade. Depending on how his Act 2 quest goes, he can actually be receptive to being made Tranquil if it means escaping the demons. After a lifetime of nightmares and embodiments of emotion assaulting him, a dreamless and emotionless existence doesn't seem so bad anymore. But if you decide to help him control his powers, he journeys to Tevinter, gets the proper instruction, and ends up taking a few levels in badass, while still being a good mage at heart.
Dwarf Fortress: Vampirism and lycanthropy, especially in adventure mode. For the former, for quite some time it was much closer to Cursed with Awesome, but more recent DF2014 updates introduced a major drawback: anyone who witnesses you feeding will flip out and go fully "no quarter" hostile. It doesn't matter how famous a hero, and it no longer depends on who or what you're feeding on. Meanwhile, werebeasts are only a killing machine during the full moon, and gain few useful abilities when in their normal form. In addition, they drop all their equipment when changing to and from werebeast form, potentially leaving them naked and unarmed in the worst possible situations. And just like the former, anyone who isn't a fellow werecreature will react with hostility.
Throught the series' lore and backstory, there is a laundry list of powers, conditions and artifacts with extremePower at a Price drawbacks. Vampirism, becoming a Lich, tapping into sources of divinity to become a god, and many more...almost all end badly for the people involved.
Several games in the series include the sword, Umbra. It is regularly amongst the best weapons in the game, dealing colossal amounts of damage and trapping the souls of those who are struck by it. However, the weapon has a mind of its own and takes over the mind of its wielder if used for long enough. Slowly, the wielder loses their identity and begins referring to themselves as "Umbra". Thankfully, this never happens to the Player Character no matter how long the sword is used.
In both Morrowind and Skyrim, the PC can be infected with Lycanthropy, turning them into a werewolf. It is considered a "gift" by the Daedric Prince of the Hunt, Hircine, bestowed upon his followers. In Morrowind, sufferers transform every night, are permanently marked for death if any NPC witnesses the transformation, cannot use/pick up items/spells while transformed, and must kill a sentient life form every night or else be severely weakened the next day. In Skyrim, the transformation is controlled by the sufferer, making it more useful in-game. In addition to the ability to transform into a powerful beast at will, the "Beastblood" makes them immune to all other disease. However, it also prevents them from getting quality sleep and requires great willpower to avoid going feral. Further, upon their deaths, the Werewolves' lycanthropy manifests as spirit wolves that drag their souls to Hircine's realm, where they join his endless Hunt. For some of the Companions, like Kodlak, Vilkas, and Farkas lycanthropy is this trope and they want to be cured. Others, like Aela and Skjor love being Werewolves and actually look forward to being part of Hircine's Hunt in the afterlife.
In Oblivion, there's the Grey Cowl of Nocturnal, a hood that gives all sorts of bonuses that a thief would love to have. The problem? It was stolen from the Daedric Prince Nocturnal, and as punishment, she cursed the Cowl; whoever owns it has their identity erased from existence. You could be wearing it and tell someone your name a thousand times, and they'll never hear it; any and all evidence that you ever lived magically ceases to be, and people will only remember you as the Gray Fox, the avatar of the Cowl. The ultimate goal of the Thieves' Guild questline is to remove the "Suck" from the Cowl. This is actually extremely useful if you remember to wear the Cowl while committing crimes. The guards will try to arrest you by default when wearing it in their vicinity but you can remove it in front of their faces, erasing your bounty and they will be none the wiser.
In Eternal Sonata, Characters who can cast magic spells capable of doing anything from super-healing to terrible destruction (as opposed to weapon-based abilities that do pretty much the same thing, with no apparent penalty) have the fiddling little downside that being a magic-user slowly kills the wielder, whether they actually use their magic or not. Also, they tend to be heavily persecuted, as a result.
Fallout: New Vegas: The Enclave Power Armour is the single strongest suit of armour in the entire game, but as Arcade Gannon will tell you, due to it's associations, wearing it in the NCR is likely to get you shot, lynched or arrested. That last one is what actually happens to Gannon if you make him wear it for the Final Battle and help the Legion win, as he's recognised by an NCR Ranger during the retreat and consequently tried as a war criminal and imprisoned indefinitely.
In Final Fantasy III, Noah gave one disciple the gift of magic, another the gift of dreams, and the third the "gift" of mortality.
Any guesses as to which of these goes bug-fuck insane and tries to unleash the game's eventual Big Bad on the world? First two don't count.
To be fair, that was a dick move on Noah's part: "Let's see, I'll make the first one a master of magic, the second a master of dreams and the third a master of dying."
Not to mention, you know, the other two prove capable of dying after all, so that "gift" was just a glorification of something he already has.
In the remake, the heroes even point out that mortality doesn't seem like much of a gift.
Crisis Core gives us the Cursed status, usually equipped to certain stat-boosting items. These items usually increase your stats by loads of points, but they make your DMW inactive, which means you can't level up or activate any of your Limit Breaks or summons.
In Final Fantasy VIII Ellone's born power does something involved with time, making her being wanted by bad guys ever since she's a girl. Her parents are killed, she gets kidnapped, being briefly placed as a science rat, and has to spend most of the game escaping from Galbadia's Army.
In Final Fantasy XIII the plot revolves around l'Cie, people chosen by the godlike fal'Cie to accomplish some mission. Being an l'Cie gives you special powers but means the most powerful government in the world wants you dead and you must figure out what on earth you are supposed to do based on a vague vision and complete it within a time limit. If you fail, you become a Cie'th, a Body Horror-tastic crystal-covered zombie-like monster that lives in eternal pain and regret, unable to think of anything but their failure And eventually turns into a rock while still suffering from horrible regret. If you succeed, you turn into crystal. And the fal'Cie can revive you to force you to do another task later. The fal'Cie are utter pricks.
Vanille, Fang, Serah, Dajh, Lightning, Hope, Snow, and Sazh all manage to get themselves back to humans. In the case of Vanille and Fang, it isn't necessarily the best thing, as they end up crystallized again.
Also, when turned into Cie'th, Lightning, Hope, Snow, and Sazh manage to "will" themselves back to normal. Said cases are very rare, however.
That was an illusion cast by Barthandelus. The "real" part of it was Orphan hoisting Fang up and torturing her. Fang transforming into the incomplete Ragnarok and the other l'Cie besides Vanille becoming Cie'th were meant to force Fang into completing her Focus according to Barthandelus' design.
Final Fantasy XIII-2 also has Paddra Nsu'Yeul, a seeress who is born into every generation, and forced to see various visions of the future. This may sound alright on the surface, but each vision actually drastically shortens her lifespan, meaning that no Yeul in history has ever seen adulthood. Then it turns out that the main character of the game has the exact same ability. Guess how that ends.
The Big Bad is also an example. He is an immortal who fell in love with the original Yeul and was devastated by her death. Thanks to his immortality, he has to watch the girl he loves be reincarnated only to die young again and again and again. By the time the game starts, he's willing to cause a Time Crash to prevent her death.
Lightning Returns: Final Fantasy XIII sees all of mankind "blessed" with an indefinite lifespan. The warping of reality by the Chaos has caused every human in the world to stop aging. So although monsters, disease and the like could still end your life, keeping yourself healthy would mean living to the very end of days. But many people end up losing any zest for life after so long (five flipping CENTURIES). Some eagerly await the end of the world, others simply commit suicide. Notable characters even have crippling emotional hangups that they've spent 500 years carrying.
In Fire Emblem: Radiant Dawn, Micaiah's "Sacrifice" ability is hyped in the storyline up as being unique and magnificent. However, in an example of Gameplay and Story Segregation, in-game the ability is only useful for level grinding and healing status effects as it uses up her HP. Combine this with the fact that she already has difficulty taking a hit and you get a very bad ability.
However, when coupled with Edward's Wrath or Tauroneo's Resolve, Micaiah becomes an untouchable, double-attacking, always-criticalling power machine, making it a case of Cursed with Awesome.
All people who have one laguz and one beorc parent are examples of this. You age slowly, gain various abilities, etc. - oh, and you are an absolute pariah in both societies. And either you can go to the Laguz (who can sense you, and probably age even slower) or the Beorc (where you have to move around every few years) societies to live. There are quite a few of these in the list of main characters.
Mostly fixed after the game ends when one of your party members goes on to found an (understandably small) nation of the "Branded", provided you recruit him... which is somewhat of a Guide Dang It
Zelgius aka the Black Knight became The Dragon because the Big Bad was the only one who accepted his Branded nature.
Ted Taylor, aka "Man-Bot", from Freedom Force was blasted by a massive dose of Energy X that causes his body to constantly generate the energy in large doses. This renders him the potentially most powerful superhero in existence, but his body is wracked by constant pain by the enormous amounts of energy that build up inside his body. In addition, he has to wear a specialized containment suit powered by his own energy emissions or the build-up of energy leads to irregular and painful detonations capable of killing people and leveling buildings. The suit is also limited in its durability, which means he's never truly able to cut loose and use his full potential either. To add to all that, he inadvertently killed his brother with one of his blasts by hesitating in taking up the suit, giving him a massive Guilt Complex as well.
In Goblin Noir casting elemental spells can affect your body in differing ways. The earth mage who explains this states that depending on the strength and complexity of the spell, it could be something as minor as a mole turning into a pebble or as major as having his soul trapped inside a pile of rock for eternity or even worse. (Willing to risk an explosion into a pile of gravel that not even your soul can survive, anyone?)
In God Hand, the main character Gene loses his right arm while saving Olivia's life, and is rewarded with having his arm replaced by the God Hand, a severed arm of a legendary fighter that grants Gene superhuman fighting skills. Unfortunately for him, the God Hand is extremely desirable to the villains, and Olivia has a bad habit of constantly sending Gene out to do her dirty work.
At the end of Golden Sun: The Lost Age, the Golden Sun event grants vast power increases to present Adepts, heals the dying Adepts present, and slows aging. Kraden, who was neither Adept nor dying, gets extremely slowed aging to the point of being immortal. Kraden was, at the time, over seventy. Nearly everybody who encounters Kraden in Golden Sun: Dark Dawn, thirty years later, comments first on the fact that he isn't dead yet, and then on how much it must suck to live forever as a feeble old man.
Zato-One from Guilty Gear. He was given a demonic entity called The Eddie that made him very powerful and granted him control of shadows. However, the process blinded him, Eddie gradually began to take over his mind, and Zato found that Eddie was killing him (and succeeded at the end of Guilty Gear X). Millia Rage is starting to have the same problems.
The titular spear in Gungnir. It's essentially a Fantastic Nuke and can summon beings known as War Gods to turn the tide in battle. However, it's only given to a successor who has the Stigmata, which is a mark that seems to be detested, the War Gods are indiscriminate about who they hit on the battlefield, meaning the tide may be turned against younote unless certain conditions (which are not given, let alone told that any conditions exist) are met, and Gungnir is said to bring destruction to the wielder.
Brandon "Beyond the Grave" Heat, the protagonist of Gungrave. The necrolization process bestowed him with vast strength and agility, as well as the ability to regenerate from most wounds almost instantaneously. And he has not lost any of the skills with firearms he learned while alive. However, necrolization has completely ruined his memories, leaving them fragmented "snippets", and he has lost a great deal of his ability to feel emotion. And because his body is not much more than a reanimated corpse (albeit a powerful one), his whole body needs fresh blood periodically. In the game continuity, Mika uses her own blood to maintain Grave's body, as she and Grave share the same blood type. The strain of having to keep Grave sustained has weakened Mika and made her anemic. The cost of having to meticulously preserve his body means having to stay sealed in a hibernation state for long periods of time, only to be brought out so he can blow something up.
Fiona of Haunting Ground was born with vast amounts of Azoth in her body. This life essence is very powerful, and allows those who use it to become immortal. It's also the reason she was abducted, and why her stalkers are out to get her.
Warlock as a class has the most examples, with cards like Flame Imp, Soulfire, Pit Lord, Shadowflame, and Succubus being very over-statted for their cost, but require losing health, sacrificing minions, or discarding cards in order to be played. Some of the more unique ones are Anima Golem, which is a 5 mana 9/9 that dies if it's your only minion, and Wrathguard, which is a 2 mana 4/3 that causes your hero to take damage every time it's hurt.
Millhouse Manastorm, while not exactly intended as a serious card, fits the bill. He's a ridiculously over-statted 2 mana 4/4, with the downside of making all your opponent's spells free for 1 turn. With a lucky hand, this will could let your opponent OTK you. Even with an average hand, they'll usually be able to clear Millhouse, play buffs or card draws or other beneficial effects, and still play the rest of their turn as normal.
Many consider Majordomo Executus to be the worst card in the game for this reason. His Deathrattle effect transforms your Hero into Ragnaros the Firelord, who has the incredible Hero Power of dealing 8 damage to a random enemy for only 2 mana. The issue is that Rag only has eight health total with no way to gain more (outside of armor-granted spells and the rare interaction with Alexstraza), rendering you basically into a One Hitpoint Wonder. And since it's bound to a Deathrattle on a card that isn't all that tough, it easily allows your opponent to kill you before you can prepare.
If Majordomo isn't the worst card, then it's certainly Cursed Blade. Cursed Blade is a solid 2/3 weapon for 1 mana. However, it has the unbelievable downside of causing your hero to take double damage while it's equipped. Not just during your turn, the entire time! That makes it hard to even gain value out of the card's stats, since attacking anything but the weakest minion suddenly results in taking massive damage. Not to mention, once it passes to your opponent it's free rain for them to deal huge burst to your face. And it's three durability is just another a downside: that means without a replacement weapon, you're stuck with Cursed Blade for at least 3 turns.
In the Whispers of the Old Gods set, several "corrupted" versions of popular cards were released, with new, interesting, and often backwards effects. Although, a few cases were simple stat changes. Naturally, this trope is played with in Twisted Worgen's flavour text.
Sometimes the Old Gods' corruptions gives you power untold, sometimes you get +1 Attack. We can’t all be winners in the Eldritch lottery.
In Heroes of Might and Magic, magic immunity tends to be this. On the one hand, the opponents can't debuff or damage your magic immune units with spells. On the other hand, you can't buff, heal or revive your magic immune units with spells either. The worst part is that most magic immune units tend to be powerful and expensive high-tier units you really want to keep alive as much as possible. On the other other hand, they also can't be harmed by the Armageddon spell...
Jak and Daxter: Poor Daxter. While being an ottsel turns out to be quite useful throughout the course of the series, he's still got to deal with being short, hairy and itching in strange places. Although when given the chance to become human again, he settles for a pair of pants instead. Being one of the Precursors is worth it.
In Kid Icarus, Pit is an angel, and has many of the powers one would expect of an angel except for flight. In Kid Icarus: Uprising, Palutena grants Pit the ability to fly with her power and guidance, but the power can only be used for five minutes at a time, after which point Pit's wings would burn off.
In both Left 4 Dead games, the survivors are all immune to the Green Flu that has been putting nearly everyone nearby them into zombie-like states. From all the story currently released, this seems to just be by luck of the draw that their immune systems can hold it off. Sounds nice until you realize that those infected appear to be in a rabies-like rage, are able to tell that the survivors AREN'T infected, and the survivors aren't immune to acid burns, strangulation, being beaten to death...
In one of the comics it's revealed that they're actually Carriers. Though they don't get turned into zombies, they are still infected and can spread the infection just by being near someone.
In Mana Khemia Alchemists Of Alrevis, there is a powerful Mana aligned with the element of Light. Someone is desperate to pact with this Mana. After they did make a pact, the Mana makes it his job to publicly humiliate his "owner", whenever the Mana is bored and in need of entertainment, which is very often. The Mana even admits it to being his hobby. Simply put, bonding with the Mana of Light cements the poor fool into becoming The Comically Serious and Chew Toy status of the game.
Biotics are people born with latent telekinetic abilities. However, in order to be born with these abilities, their mother must be exposed to potentially lethal doses of Element Zero. Then there's a 70% chance the fetus will die of tumor growth. Then, the real problems begin. In order for the abilities to be anything but useless, Biotics must be outfitted with Biotic Implants which allow them to focus and control their abilities. This can lead to painful migraines and insomnia, and that's the least of their problems. In the early days of the Alliance, many biotics were drafted into a special military program, forcing them to go through Training from Hell to get the best use out of their powers. Kaidan will tell you about it if you ask him.
We also have the Drell, a race of reptilian-humanoids who can remember every moment of their life with perfect clarity (except for their birth due to it being so traumatic). They are able to feel sensations of past memories including kisses and other intimate moments, but also any physical or emotional pain. There is also a risk of Solipsism, trapping a Drell in a certain memory, this can include horrific experiences including gunshot wounds or torture. As Thane puts it, "Remembering times I've taken bullets is... unpleasant"
In Metal Gear Solid, we got PsychoMantis. He outright stated in his death speech that as a kid he was unable to shut off his powerful Mind Reading ability (learning that his father hated him, ouch), and that apparently looking too deep in too many minds of Serial Killers drove him off the deep end. Apparently he still couldn't completely shut off his Telepathy since he asked for his mask back to block the voices out, and complained about "How everyone thinks of only one thing". No wonder he became a Psycho!
Fortune in Metal Gear Solid 2: Sons of Liberty lost her father and her husband in quick succession, and the stress caused her to miscarry her unborn child. She was suicidal, but discovered she could not die: no matter how improbable, events conspired to keep her alive, cursing her with good luck. Explosives are duds, and bullets miss. At first, this ability came from a device on her that she didn't know about, and a shot actually hits its mark once Ocelot removes it, but right before she dies, she invokes the power naturally to save the Snakes and Raiden from Ocelot's Metal Gear RAY assault.
Metroid Prime 3 has Hypermode, a Limit Break in which Samus becomes very powerful and invincible. But, it involves Phazon, so excess use of it will cause Samus to get slowly corrupted, and if corrupted far enough she will become terminally corrupted, leading to a Non-Standard Game Over.
Not as bad as it seems, because overusing Hypermode puts Samus into corruption mode, where terminal corruption is averted by expending excess phazon... by continuously firing the hyper beam just like in Hypermode. Essentially the only real suck is for anything that happens to be in her way, but then that's par for the course with Samus.
In-universe, it's implied that as Samus uses Hypermode more, the severity of her long-term corruption increases, but thanks to Gameplay and Story Segregation, this happens at the same rate regardless of how little or how much you use Hypermode.
Samus Aran herself is blessed with suck: found and raised by Precursors, she is able to manufacture by herself a personal gunship which can travel through the galaxy and has some impressive firepower, can hack through any computer, and is such a badass that the most ruthless, militaristic, intelligent race of the universe crap their pants when she is in the same solar system. On the other hand, she has to clean up the mess produced by the Chozo, will probably have to fight in a galaxy-wide conflict until her death, and any planet she sets foot on tends to explode.
It is also implied that, while immortal, his abuse of time-travel has left his body withered and left his mind in a warped, child-like state (See Enfant Terrible).
Porky's fate isn't too bad for him, though, because it's exactly what he wanted. The epilogue even states he's happy.
In Neverwinter Nights 2: Mask of the Betrayer the Knight-Captain is afflicted by a curse that requires him/her to periodically consume spirits or die. Whether this is Cursed with Awesome or Blessed with Suck depends on how you approach it. An evil character who sees it as a blessing and eats spirits willy-nilly will find that the more souls they eat the faster they die. However, if the KC approaches the curse with practical restraint (pushing one into good-aligned, especially since taking that approach can net you a power that allows you to quench your soul thirst on undead, of which there's no shortage) s/he can get most of the benefits with few of the side effects.
Emil from NieR, who gains tremendous magical powers, at the cost of his appearance; it turns him into a floating skeleton with a permanent Slasher Smile stuck on his face. Before that, his eyes could petrify anything he looked at...but he had no control over it. He ended up wearing a blindfold everywhere.
Oswald the Shadow Knight from Odin Sphere is the wielder of the Belderiever, an extremely powerful magic sword that gains power from killing and allows him access to a near-unstoppable shadow form. Unfortunately, the sword is cursed and the shadow knight power-up came from having Oswald's life and soul sold to the Queen of the Dead (and to make matters worse, Oswald himself didn't get any say in the matter; his foster father did it to him without his consent). Not only is he hated by every living being on the planet and plagued by weakness as the dead pull on his soul; he cannot let the Belderiever out of sight or the shinigami will hunt him down, kill him and drag his soul to the underworld where he'll be the Queen of the Dead's plaything for all eternity.
In Persona 3, to gain and control an all powerful shadow-fighting Persona, you must have the "potential", a trait only a few are born with. If you don't, it can be artificially-induced but you have to take experimental suppressants with deadly side effects, and if you ever forget to take them, your own Persona will try to kill you or anybody around you. There is also a slim possibility to lose control of a Persona even if the ability awakens naturally. The poor guy with the worst of it is Shinjiro Aragaki. He leaves the group out the safety of his friends (and they still want him back anyway out of worry), forced to make shady deals with an assassination group to obtain above deadly suppressants because he learned the hard way what happens when a Persona goes berserk by accidentally killing an innocent woman, you know who's blessed with suck. The son of said victim wants him dead
Then the PSP port/remake of Persona 2 : Eternal Punishment (Which wasn't localised) reveals in the newly added Tatsuya scenario another possible outcome to giving someone artificially given a Persona. Their psyche can get fractured and they can turn into demons
Certain Personas can be this depending on their skills. Some Personas only sport physical attacks which deplete health (which draws you closer to Critical Existence Failure), or Personas who only sport status spells, since being offensive and tactical is usually required by the persona you have equipped. And if you'd guessed, spells are element based, and shadows sport elemental weaknesses.
Planescape: Torment. The main character can rise up from the dead every time he is killed (Which is even used in a few quests to your advantage), but loses his memory of his previous life (except for the incarnation you control, but that's because of external phlebotinum), and every time he comes back from the dead, someone else dies in his place. Oh, and he also brings tormented souls to him, which are caught in a cycle of tragedy. The fact he did this to correct something he did which is causing the Multiverse to slowly die means the attempt has backfired spectacularly.
In more recent Pokémon games, certain natural abilities can function this way.
For instance, Klutz is an ability which doesn't prevent the Pokémon from holding an item, but rather from using it. This means that they can't pump up their attacks with stat-raising items. On the other hand, negative-effect items like Toxic Orbs and Iron Balls also don't affect them, and the Pokémon with Klutz can learn moves that make good use of those items. This ability is ineffective around Mega Stones as well, which provides those inadequately equipped to utilize Klutz another way out.
Pokémon Mystery Dungeon makes it so that each Pokémon has ALL of its possible abilities simultaneously — good and bad — AND makes several once-decent abilities worse. So now, Klutzy Pokémon can't hold ANY items, even the few they could before...Run Away makes a low-HP Pokémon panic and go into an uncontrollable retreat...and so on and so forth.
Lucario (and its pre-evolution Riolu). In the main games, it either gained speed when it flinched (the Steadfast ability) or was immune to flinching (the Inner Focus ability). In the second pair of Pokémon Mystery Dungeon games, Steadfast is completely nullified by Inner Focus.
Not completely nullified; in the Mystery Dungeon games, Steadfast is also activated by the 'Cross-Eyed' status. However, there are no moves that initiate Cross-Eyed, and there are very few dungeons where the enemy Pokémon are smart enough to throw the seed at you, so you almost have to eat it yourself.
Non-ability wise, we have Absol. Absol have the extraordinary ability to sense impending natural disasters, and naturally try to warn humans who are in danger. Humans mistakenly end up thinking that Absol cause the disasters and hunt them down for it.
Psyduck needs to have a painful headache to even use its powers.
Skitty and Delcatty can have an ability called Normalize. This ability turns all of its moves into Normal-type moves. While it gets an attack bonus for being the same type and it is able to use Thunder Wave to paralyze Ground-types (Which are normally immune to Electric moves), this means that it is unable to counter things that Normal-types are weak to, and it cannot touch Ghost-types at all.
Darkrai is also blessed with suck, as its Bad Dreams ability automatically does damage to any Pokémon who is sleeping. In some forms of Pokémon media, this is portrayed as a curse which has caused him to be feared and unable to interact with friends.
Then of course there are "abilities" like Truant (only able to attack every other turn), Slow Start (Attack and Speed are halved for the first 5 turns) and Defeatist (Attack stats are halved if HP drops to 50% or less) which only serve to limit Pokémon that would otherwise be Game Breakers, except for Durant, which can instead use Entrainment to give its horrible ability to the opponent.
For certain Pokémon designed to fill a niche, their typing can often screw them over. For instance, Avalugg is designed to be a physically-oriented Stone Wall/Mighty Glacier, with its highest stat being Defense... unfortunately, it's an Ice type, one of the worst defensive types due to it having a resistance only to itself, no immunities and four weaknesses, limiting its usefulness.
It was even worse in the years when whether a move was physical or special depended on its type and not the move itself. One example is poor Absol, who has a high Attack stat, but couldn't use it with STAB in Gen III due to all Dark type moves back then being Special in nature.
Etna: You've got until tomorrow morning. Understand? If you don't bring me the Ultra Dessert by then, well... you'll be begging me to let you explode! Capiche!?
In Robopon 2, Mushroom effects in the second game basically fall into this. There are three different mushroom colors, and each one has a specific set of spirits that can be summoned from it. Since the spirits are chosen at random, one may get the spirit that makes stuff cheaper at shops... or the spirit that throws stuff out of your inventory for no reason.
In Runescape, Zanik is truly Blessed With Suck- she's a Dorgeshuun goblin, a tribe that fled beneath the earth when their god, Bandos, ordered them to fight a war they couldn't possibly win for fun. Zanik is the Chosen Commander of a prophecy, who will lead goblins to victory all over the world. However, the Dorgeshuun have become learned pacifists, so to fulfill the prophecy, Zanik would effectively get her tribe and thousands of other goblins killed. The entire quest 'The Chosen Commander' involves the player helping Zanik to escape this destiny, and after the two defeat Bandos, Zanik's friend Juna, a giant snake who is a Guardian of Guthix (god of balance), ends their friendship because of Zanik's defiance of her destiny (Juna says that since Bandos brought the goblins to Runescape, they were his, so Zanik should have gone along with him), which makes Juna a really nice "friend". Though to her credit, Juna does later acknowledge her way of thinking was wrong and wishes to apologize to Zanik.
The entire race of Mahjarrat have immortality and incredible sorcerous powers but they are forced to fight among themselves eternally as their immortality comes from absorbing the life force of the weakest among them. Those who have gotten sick of the violence have thus far all been consumed.
In the quest "The World Wakes", the player is granted semi-divine power as the "World Guardian", along with the job of preventing gods from killing or abusing the mortals of Gielinor, such as starting a new God War. The good news? No god's power can affect you directly unless you grant them permission ("Fate of the Gods"), and your special fate gives you Resurrective Immortality. The bad news? Sliske, a mortal Mahjarrat, started a new God War anyway by distracting you ("Missing Presumed Death"), you're still capable of bad decisions and misplaced trust, your friends and loved ones are fair targets, Sliske could still kill you and enslave your soul to prevent your resurrection ("Kindred Spirits"), and your immunity to gods can be outpowered by Elder Gods like Jas ("Sliske's Endgame").
Silent Hill has the horrific example of Alessa. She was born with vaguely-defined psychic powers that included a link to the supernatural and "doing things" with her mind. As a result, she was tormented and abused by her peers and her mother, burnt alive as part of some unexplained ritual at the age of seven, then spent the next seven years in unending agony from burns that wouldn't heal, all for the purpose of summoning a being that would plunge the world into darkness. Even when she finally dies, she's merely reincarnated into another body so the ritual can be completed, with the implication that this will continue forever.
Zasalamel in SoulCalibur III finds a way to render himself immortal, allowing him to reincarnate with all of his past memories and personality intact after each death. However, this also robs him of a peaceful death, and his soul gets eaten away by dark forces after each death and rebirth, forcing him to search for the game's MacGuffin in order to break the cycle.
In the following game, he comes to terms with his immortality after a vision of the future. He decides that it's better to use his "gift" to guide humanity down a good path and the end of his ending shows him living in the present day as a wealthy man, pleased with his progress.
Suikoden: Possessing a True Rune stops the character from aging any further and comes with a whole host of really spiffy abilities besides. Sounds great...but True Runes also seem to possess a will of their own, and tend to "force" their bearers into conflict. On top of that, there are many cases where even using (or unlocking) a True Rune's full power causes an additional nasty side-effect. Some examples:
In Suikoden IV, the Rune of Punishment can be used to dismantle enemy fleets...but it drags its bearer a step closer to death each time it's used in this way. It's implied that the Rune itself orchestrates events around the bearer, so that they're FORCED to use it's power. Mostly in the form of making huge armies or powerful monsters attack the home of the bearer and easily threatening to kill everyone the bearer knows and loves. It only stops doing this if it goes into it's "Forgiveness phase" (as the Rune of Punishment governs both punishment AND forgiveness), but this is apparently an exceedingly rare event.
The Rune of Life and Death in Suikoden earns its name by causing people close to the wearer to die and then eats their souls. On the plus side, the Soul Eater allows its user more powerful abilities every time it does so.
Suikoden V's Sun Rune causes mental instability along with wielder-willed climate change.
This is inherent trait of the Rune, but it can be controlled by possessing the Dawn and Twilight Runes. Consequently, possessing all three runes mitigates the side effects of bearing the Sun Rune. Too bad that the Dawn Rune had been stolen before the start of the game...
The Moon Rune turns its bearer into a vampire. It takes a few years to get their bloodthirsty instincts under control, by which point they'll more than likely have killed a lot of people.
The Rune of Beginning is usually split into its two component halves, the Bright Shield and Black Sword Runes, which are usually borne by two close friends. The two Runes will compel their bearers to put them back together again by slowly draining their lives. To be merged back into the Rune of Beginning, the bearer of one must kill the bearer of the other and take their Rune. That's right, the Rune of Beginning will force its bearer to kill their own best friend.
Remember Luca Blight? It's very subtly implied that one of the reasons he was so Ax-Crazy was because of the influence of the Beast Rune, which governs over "passion and bestial rage". It eats people to power itself.
The elemental Runes actively try to cause their bearer to lose control of them. We don't know what happens with the other four, but when this happened with the True Fire Rune, it caused a massive explosion that wiped out both armies in the conflict it was being used in at the time and continued to burn for seven days and seven nights. And True Water froze the entire area of Sindar Ruin when it just lost control for a little bit.
We know that Luc gets some fun post apocalyptic images/messages from his rune. And these are the ones that lack well documented curses!
The Night Rune currently takes the form of a talking sword. It's also rather arrogant and mouthy, and is prone to attacking its own wielder over real or imagined slights.
The King of the Sindar is the current bearer of the Rune of Change. As a result, the entire race is incapable of settling in one location for very long.
Conversely, The Circle Rune is in the hands of the Holy Harmonian Empire's High Priest Hikusaak has the powers of order and stagnation. Yeah, they've had centuries of peace and prosperity, but a decent chunk of their human population are little more than slaves, and the non-humans should be so lucky.
Harmonia goes even further with this trope given its tendency to revert to artificial human creation to maintain power. This is considered less than successful given that Luc is willing to blow up a continent to end the rune's hold of humanity.
And proof you don't even need a True Rune to fit this trope in the game: Thomas. You'd think given he was bastard child given hold of a castle to keep him out of the way after his mother's death that he'd have some well deserved angst.
Although it's a side-story, Suikoden Tierkreis gives us Manaril, the pre-teen princess of the Magedom of Janam. She is blessed by the chronicles to be a "reader", which allows her to transcribe the text of Chronicles. This power is capable of making great magical (mage arts) or technological (developing firearms) advances, but the strain of reading is so great that a reader typically dies within a few years of doing it. She is also forced to do this by her own mother. Fortunately, the mother allows Manaril to leave following a coup, but still, damn, that girl has it rough.
In Spandex Force 2: Superhero U the elderly Infinitorax Supreme's only superpower is immortality. When the player character asks what else he can do, he replies that he can bite really hard.
One of the reasons Pichu in Super Smash Bros. Melee is placed at the very bottom of the tier list is that most of his electrical attacks damage HIMSELF as well as their targets. Combining this with the fact that he's one of the lightest characters in the roster makes him a very easy target to smash from the ring.
Luke fon Fabre was born with the ability to cause hyperresonances on his own, which makes him a borderline Reality Warper. However, hyperresonance is so destructive and difficult to control that it's almost useless in battle because he risks blowing everything up, including himself. His status as The Chosen One also results in the original Luke being kidnapped and replaced with a replica, and the replica ("our" Luke) being manipulated into destroying a city of ten thousand people.
Tales of Symphonia Colette's powers as the Chosen gives her pretty hard light wings and a cadre of powerful & useful Angelic Attacks. However she loses some aspect of her humanity every time she gains another power including the ability to taste, sleep, and eventually her voice. She eventually loses her soul but luckily she loses the bad side of this without having to lose the cool angel powers.
She also later suffers from and extremely rare illness related to her powers which starts turning her body into crystal. Her powers remain the same, but being the type of person she is, she never tells anyone about it until it gets too big to hide anymore. A substantial portion of the game is dedicated to you trying to cure her.
Since the world of Touhou overflows with people with special powers, this trope tends to pop up a little bit here and there:
One of the most infamous examples is Rumia, who has the ability to create darkness around herself, but lacks the Required Secondary Powers to see through it. She seems to have at least better night vision than normal, so she's still better off in her darkness than a regular human, but not good enough to avoid crashing into things while flying with her field of darkness around her.
Shiki of Tsukihime gains the power to "understand the concept of death" after a near death experience as a child, which allow him to destroy almost anything within knife range, bypassing a variety of forms of Nigh-Invulnerability (including reincarnation). Unfortunately, it also reduces his lifespan and health (somewhat variable depending on the route), puts increasing amounts of strain on his brain (to the point of threatening to explode the blood vessels) as he perceives harder and harder to comprehend deaths (buildings, undead, poisons,... ), and can't be turned off. Seeing a world that can seemingly crumble at a touch was terrifying when he first woke up with his power; if he had not been given indestructible glasses to block his eyes, he would have probably gone insane soon after. And his eyes will eventually become too powerful for even those glasses to contain...
Arcueid has this as well; she is easily the most powerful character in the game, able to summon a phase of the moon that will only happen 1000 years in the future into present reality. Anybody who she gets serious against will not last long. Problem: she has to use over 70% of her power to hold back her vampiric blood-lust from taking over and massacring everything.
There are a few things like this in World of Warcraft, but a particularly interesting one is the case of Sir Zeliek, a paladin so strong in faith that even after a necromancer killed him and raised him as a Death Knight, he retained his own mind, will, and his paladin powers. Problem is, the necromancer does control Zeliek's body, so while most of those who became Death Knights unwillingly (itself considered a Fate Worse Than Death) are obliviously Brainwashed and Crazy, he is a fully-awarepuppet who begs forgiveness whenever he kills a player.
Taken a step further as his Light based powers cause him extreme amounts of pain whenever he's forced to use them.
Being a healer in PvP can evoke this feeling as any smart opponent will aim to take you down first. And then you get blamed by your teammates for not healing them.
Magic has a hint of this in the backstory, as it attracted the Burning Legion (and hence most of the trouble the world has seen) to Azeroth to begin with.
Freeware game Wrath of the Sea King has a premise built on this trope. The main character is a treasure hunter suffering from headaches so strong, they are driving him to suicide. He finds an amulet that makes him immortal - but the headaches remain, and he feels the pain of each death. Ouch. In the game itself, it translates in having unlimited lives, but the twist is, you have to die as less as possible if you want to see the complete ending sequence.
In Zero Time Dilemma, some of the characters are SHIFTers, which means they can transfer their consciousness to alternate timelines. Sounds like a nice power to have, but later in the game it's revealed that every time they jump to an alternate timeline, their consciousness enters the body of their alternate selves. That alternate self's consciousness is then transferred back into your original body in your original timeline. Since the time where you're most likely to want to SHIFT is when you're about to die, this means that from your alternate self's point of view, they were suddenly teleported into a deadly situation with no warning. Your alternate selves also have this power, and they can use it on you, so it's only a matter of time before one of them decides to swap with you, and you find yourself suddenly stuck in a Death Trap with no warning.
Klonoa is a Dream Traveller. His job allows him to travel to fantastical worlds, going on adventures and making friends along the way. This sounds wonderful until you realise that he has no license to stay in or go back to any world he visits - all goodbyes are final - and it's not even clear if he retains any memories of the worlds he visits either, as he never once mentions Huepow or the events of Door to Phantomile in any subsequent games. Klonoa is not a happy character.
Eternal Darkness has the eponymous Tome of Eternal Darkness, which grants the wielder the ability to read it no matter their own literacy and mother tongue, the ability to cast spells, an instant recap of everything that has happened to every previous wielder of the book, and the quite handy ability to write down anything they wish in the book for future generations to read, effectively making it the best weapon in existence in more ways than one. The drawback ? It forces you to take part in a cosmic scheme that will likely end with your death, possibly much worse, you may not like what you get to see, and you have no choice but to pick up the tome and carry the will of its maker, whether you realize it or not.
The Legacy of Kain: Soul Reaver introduces the titular sword, which has both a material and immaterial form. Being able to cut people so hard their soul gets hurt may sound like a good deal at first, but the thing will start absorbing the wielder's own life energy if it doesn't get what it wants, and it having a mind of its own mean the wielder doesn't even get a choice as to whether he wants it or not. It essentially traps them in a constant cycle of finding more people to murder to keep the weapon satiated, lest they end up eaten alive by their own weapon.
The protagonist of the Soul Reaver series is also a good candidate, seeing as he is blessed with a pair of wings only for them to be torn off and his body tossed into a pit where he gets to suffer eternal anguish for almost all of eternity, until an elder god pulls him out and makes him a slave, where he is once again blessed with suck - being able to shift through dimensions and making him practically immortal so long as he keeps feeding on souls. The suck part being that he also gets to be stuck in a permanently rotting carcass without a lower jaw that hates itself and absolutely everyone around it.