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.
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.
There is a sword, Umbra, of which is debatably the best sword in the game, doing large amounts of damage and trapping the souls of enemies you fight, but it seems that if you would use this weapon long enough, it erodes your sanity, causing you to be aware of Umbra's hunger, and eventually the line between sword and wielder blurs. The character you receive Umbra from in Morrowind and Oblivion both refer to themselves as Umbra.
In Oblivion, there's the Grey Cowl of Nocturnal, a helmet that gives all sorts of bonuses that a thief would love to have. The problem? It was stolen from the Daedra Lord 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.
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.
The ultimate goal of the Thieves' Guild questline is to remove the "Suck" from the Cowl.
Becoming a Werewolf is this for some. Lycanthropy technically is a blessing, being a "gift" from the Daedric Prince of the Hunt Hircine that imbues a mortal with Hircine's power. Werewolves can transform into very powerful beasts and have total immunity to disease, though their beastblood prevents them from ever having a good night's sleep and they have to maintain great willpower to avoid going feral. But 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.
The whole series in general has a laundry list of powers, conditions and artifacts with extremePower at a Price, which the lore characters with them pay hard. When the player gets in on the fun there's an equally long list of Good Bad Bugs that can be exploited to overcome the drawbacks.
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 Nonstandard 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.
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.
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:
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.