Mistakes uses the common Fanon that the nations can't be killed by physical means and can recover from apparent "death". This isn't necessarily a good thing when the fic starts with China being effectively gang-raped to death and being thoroughly traumatised on waking (particularly since up until it happened to him he thought rape was unimportant and murder actually fun, since human morality doesn't quite work with immortals), and the Koreas trapped in Unit 731, where they get dissected again and again and again ...
A Naruto/negima crossover Fanfic, "Broken Faith" by Kur 0 Kishi has this in spades. Naruto inherited Kyuubi's core and powers before he came into Negima's era, giving him an extended and merged version of the red Kyuubi Cloak and the golden Kyuubi Chakra Mode from Canon manga with awesome horns and chains to boot, rendering him a literal God on the battlefield. The problem? It burns his flesh away every second unless he reinforces his organs liberally with Chakra first, to the point where people start to see his bones if he holds the mode up for anything longer than 5 minutes.
Doubled in that using the power accelerates the breaking of the seal that's keeping him weak and stuck in Mahora. Sounds good doesnt it? It isn't, if the seal is released in a way it wasn't meant to ( i.e ; Breaking), both him and the tree go up in an explosion powerful enough to kill Divinity, taking everything in radius along with him at the same time.
Happy Families Are All Alike (A Naruto/Negima crossover) has Naruto himself. He has superhuman senses and is physically outstanding. He has powerful regeneration... but the Kyuubi has, as of chapter 7, firmly planted its feet in the Eldritch Abomination category. And it can tear his flesh apart in his dreams.
The Harry Potter/Marvel Universe crossover A Third Path to the Future has Melody, a young mutant whose powers change anything she sees into music. While Melody can tell exactly what kind of person someone is by the songs she hears from them, her mind has been so warped that she has no concept of numbers and has little to no social skills. According to Xavier, she's one of the few mutants in The Vault simply because she's incapable of taking care of herself.
The Three Kings: Hunt: The Mages who get magical powers which can be very powerful but are also very specific and limited; these same powers also cause mages to be hunted down and killed by wizards. Further according to the author not all these magical powers are very useful (for example generating globes of light)
In the AULies On Light Yagami is revealed to be an incubus on his eighteenth birthday meaning he's really a super-powered immortaldemon and that he can't go anywhere without someone trying to molest him. Also if he ever wants to pursue a relationship with anyone he has to take extra care not to suck out their lifeforce.
In Dragon Age: The Crown of Thorns, the dwarven noble protagonist is a Magic Knight because he harbors a tear in the veil. Unfortunately, while he did get some of the spirit warrior abilities form Awakening, this seems to have caused at least as may problems, some of them rather serious.
Red Lightning gives us Jimmy, who is completly immortal. Unfortunatly, this rendered him unable to sleep or fall unconsious, and did nothing to numb his sense of pain. The poor kid had to stay consious while pulling himself back together as atoms in the air!
A Type 4 appears in Mad World. Lauren develops a rapid Healing Factor. While normally this would be really good, the sucky part comes in when she's captured and tortured by really sadistic Fae. Her wounds healing just means he can torture her again. He quickly abandons regular physical torture, in favor of doing something where even if Lauren could heal, her injury would only instantly reoccur. Her injuries are later compared to those associated with crucifixion. She, understandably, starts to loose the will to live.
The Powers Of Harmony: Rarity's insomnia inspires some of her best work, but leaves her very weak (due to denying her magic font time to recharge). Turns out, Harmony engineered this as part of her master plan.
Twilight's Power Copying ability works by making her more sensitive to magic, which makes it easier to understand how spells work. However, this also makes her more vulnerable to corruptive magic, which leaves her weak and sick.
Discord made the Princesses immortal and linked them to the Sun and Moon because he knew that eventually they'd snap from the pressure of maintaining the world and succumb to the Chaos magic inside them. This is what led to Luna becoming Nightmare Moon.
Within the Tripytch Continuum, A Mark Of Appeal kicks off when a character supposedly "blessed" with ludicrous sex appeal comes to Luna with a simple request: to destroy her mark, magic, or both. (The short version: her talent has been growing stronger as she's aged, starting from simply inspiring look-here-first interest in her classmates. It's currently at the point where it crosses gender and species barriers within minutes, creating infatuation, arousal, and then steadily strengthening both. Anyone she tries to speak with will eventually attempt seduction — or worse — and she can't turn it off.) However, the act of directly changing or destroying a mark is beyond even alicorn magic... With the most recent chapter, it's been implied that the curse may actually be genetic, as her parents appear to have had their personalities consumed by their own talents....
In A Growing Affection, Kohaku's status as a living Perception Filter is this. Her parents forgot her when they went on vacation a couple of times (doesn't help that its a big family), and she gets passed over for promotion because no-one can remember what she did in the Chunin Exams. But as a ninja being functionally invisible and easily forgotten is a very useful ability.
In Shinigan Naruto gains the Mystic Eyes of Death Perception. While his power has a surprising amount of versitility (such as removing Sasuke and Anko's Cursed Seals with a senbon), for a long time anything he looked at with his Shinigan on would fall apart along the lines. Seeing walls and furniture randomly collapse in pieces freaks Naruto out but seeing people falling apart nearly drives him insane. Naruto had to learn the Bringer Of Darkness technique to temporarily blind himself to save his sanity.
All of the hybrids in Morphic to a greater or lesser degree (it is a Deconstruction after all), but Gabriel, a half-Slugma really takes the cake.
Doubled. Not only is he half a blob of magma, but if he absorbs mud or stone, he begins to heat and get fire powers, which is awesome ... until you realize that, as his body temperature goes up, major tissue and organ damage would result. He's only (part) human. Special mention also goes to Mia Kerrigan, the Scyther morph, who has bladed arms, lightning reflexes, accelerated growth and is probably the strongest of the cast ... but does not understand empathy, limits or self-control.
Also of special note is Katherine, who was mixed with a Roselia. She ages even faster than Mia and is considered the smartest and most mature of the hybrids... But apart from the fact that she actually dislikes aging so quickly, she also has roses for hands, which makes even simple tasks like holding a pencil difficult. She also gets tired easily if she's not near sunlight.
The dampener removal in Poké Wars. Its removal greatly augments a Pokémon's abilities. Unfortunately it is extremely painful and results in Power Incontinence until they learn to control it. Even then, their attacks are always lethal and extremely destructive. It is especially magnified for Pokémon like Heatran or Magmar whose body temperature rises to such an extent that being near them is lethal.
John has complete control over water and the body of a winged god; but he also has to eat constantly, has ears so sensitive that he folds up in pain at shrill sounds that don't bother other people, and, worst of all, is no longer human, so he's no longer sexually attracted to human women.
Paul is so ridiculously strong that he can barely move without causing chaos. He has practiced day and night to get to the point where he can walk around, but he has to keep constant watch on himself, keep his arms at his sides, etc. To revert to his more manageable lower level of strength, he has to explode, creating a large glassy crater and wiping out everything around him. Even at his lower level of strength he has to fiercely regulate his behavior. In neither case does he dare have sex. Oh, and neither of them can go home as is...
A Semblance Of Failure is set in a support group for people with useless Semblances. One guy can tell exactly how much salt there is in his proximity; another can grow her nails at an accelerated rate.