The mermaids on H2O: Just Add Water. If you think about it, turning into a mermaid when you are exposed to water sucks major ass if you have to keep up The Masquerade. What happens if it's a rainy day, or if you have the power but live in a really rainy area? What happens if it's summer and you and your non mermaid friends/family want to go swimming? What happens if you need to shower, wash your hands,? What if you want to join the swim team or be a life guard? Remember when the one girl got hit full-on by a sprinkler when out and about? Maybe you could hide it for a short time, but not forever, and not without some kind of consequence.
Even their other 'water powers' aren't always great. Cleo, Rikki, and Emma could kill someone in seconds, as well as move, boil, or freeze water (respectively). And, um... what exactly can Bella do? Oh, right, make pretty statues and jello.
River Tam from Firefly (and its sequel movie, Serenity) is an obvious case. Government experiments gave her mind-reading abilities and Waif-Fu ... the side effect? She's traumatized and schizophrenic. It is also implied that she had latent psychic powers before being "recruited." That and her genius-level intellect led to her being selected for the Academy, which leads to a double-whammy of being Blessed with Suck.
Hurley from Lost sees his ability to communicate with dead people as a curse, until Jacob manages to convince him otherwise. He becomes way more cheerful from then on.
In addition, his winning the lottery using The Numbers. Yes, he's worth hundreds of millions of dollars, but bad things happen to the people around him.
Richard describes his immortality as this.
Non-superpowered example: In New Tricks, Brian 'Memory' Lane is a brilliant detective with an instant-recall Photographic Memory that lets him rattle off not only the details of long-unsolved crimes but also the full career histories of the investigating officers involved and makes him a near-flawless investigator... if not for the crippling Obsessive Compulsive Disorder that it stems from, which if he doesn't adequately medicate himself leaves him a manic depressive and obsessive paranoiac who is almost entirely unable to function, and when fully medicated at the best of times renders him an anti-social and anal (if essentially decent) pedant.
Very similarly, Adrian Monk from Monk is a brilliant detective, but suffers a severe case of Obsessive Compulsive Disorder (and many, many other problems). Unlike Brian, he does not need medications to function normally, but must overcome or circumvent issues that directly interfere with his need for everything to be clean, sanitary, and perfectly arranged by shape, size, and color. Monk fulfills the Blessed With Suck trope in that, if he does take medication to suppress his OCD, his investigative brilliance goes with it. His brilliant detective skills made him an enemy, resulting in the death of his wife, the only thing in life that ever really made him happy. As he frequently says on the show, "It's a gift...and a curse".
If Dr. House does anything to take away the pain in his damaged thigh muscle, he loses his brilliant diagnostic ability. He believed that's why he couldn't take stronger/safer drugs to dull his pain, however...it's actually more that his injury made him more open to using his cynicism and lack of trust in people as tools in his diagnostic work. Now he's actively trying to NOT be a jerk, still with severe leg pain (he claims it's numbed by obsessing on things like cooking or medicine) and the same human petri dish of lying patients. Ouch. It's his own discomfort he's choosing to create at this point.
One patient of the week had perfect recall stemming from a case of OCD. It had essentially ruined her life — she remembered every single bad thing her sister had ever done to her, and, worse, because she remembered it, she could count all the bad things and compare them with the good things...and found out that her sister had hurt her more times than she'd helped her (which, them being kids when most of this happened, wasn't the sister's fault and was never on purpose, but still). Because of this, she cut off all ties with her sister, because she reasoned that her sister did her more harm than good, and couldn't bring herself to act happy around her sister no matter how hard she tried. Relationships with people were essentially impossible for her.
Being the Slayer has granted Buffy incredible levels of physical strength and endurance to the point where she's almost a superhero at times - however, the duties that come with being the Slayer have destroyed any chance she has of living a normal life and almost guarantee that she will die a premature and most-likely painful death at the hands of her enemies.
And then there's the time Buffy gains the ability to read minds, with less pleasant effects than she expects at first.
In the Buffyverse, the vampire soul can be seen as a form of meta-Blessed With Suck; its acquisition is the cause of Badass Decay, after all.
Angel is definitely Blessed with Suck (or cursed) after he's given his soul back. His soul makes him human again (personality-wise) and prevents him from being a total monster and torturing and killing everyone he comes across like he does when he's Angelus, but the point of the curse was to force him to live with the horrible guilt of all the destruction he's caused for an eternity, and when he finally experiences true happiness (which he only does thanks to the curse), he loses his soul.
Season 8 has Buffy become Superman. Everyone's happy about this, except for Dawn, who expects more power will lead to bigger consequences.
Drusilla gift of precognition sounds pretty cool, except it was what convinced Angelus that he wanted to torture and break her. It also results in stomach pains, fainting and knowing unpleasant things about the future that she can't change. It was actually a vision that Spike would fall in love with a Slayer which led her to become disenchanted with and leave him.
Ted uncontrollably emits deadly radiation when feeling emotion, which results in the death of his wife. He could also destroy a city, the aversion of which becomes the main plot of season 1.
Peter is one of the few (well, two, the other being Hiro) Heroes who knew how his power had the potential to help people...until it's revealed that being able to absorb so many powers could make him unstable and explode. He then becomes another Blessed with Suck believer.
Having any superpower means that it's only a matter of time before either Sylar or Primatech Paper tracks you down, so anyone without truly awesome powers to fight them off has been royally Blessed with Suck.
In Season 2, we meet Maya and Alejandro. Maya's ability is, essentially, to kill everyone within ten feet of her if she gets upset. She once kills a whole town when he power first manifests. At a wedding. Alejandro's ability somehow reverses this... sometimes. A few tropers have therefore dubbed them the "Blessed With Suck Twins".
Adam's power seems to be Blessed with Suck as well given that he's immortal and can't die, and in the second volume finale Hiro left him inside of a buried coffin, despite the fact that Hiro originally planned to chop off Adam's head (which would have killed him for real). Just more proof that you should Beware the Nice Ones.
Season 3 reveals that Sylar's original powers fit here as well. It turns out that his power to instantly understand how something works also comes with a hunger to understand things. For Sylar, this means being obsessed with Mega Manning as many powers as possible, fitting right into With Great Power Comes Great Insanity. Seems every power has some Suck to it.
Hiro himself. In season 1, he can handle freezing time okay - but takes an insane amount of concentration or a good motivation, but he was unable to teleport WITHOUT accidentally travelling in time as well.And we learn in Season 4 that extensive time travel will give you a tumor. Arnold, Samuel's resident time-traveller, died from a tumor, and Hiro has a few months left to live.
Done to a particularly nerve wracking degree in Charmed. All good witches have some sort of supernatural power, a power that can be used to make life easier, right? Wrong! For those of the good alignment, personal gain is strictly forbidden, and abusing one's powers can have the potential of them being taken away. Evil is free to do whatever they want however.
It's actually one of the points of Wizards of Waverly Place. The Russo siblings possess power every youth would wish to possess. Problem? Only one of them can keep it, so that generated a rivalry who broke their harmony. You can say than because of that, the whole series is a fable about family bond and responsibility.
Jack Bauer of 24 could qualify despite not having a superpower (other than the power to be badass). He's an incredibly talented CTU agent, the best in the field, the go-to guy for every problem concerning National Security. At the cost of his family, friends and co-workers getting killed, being a wanted man by terrorists and foreign nations, and virtually having no chance at a normal life because when he tries, he always gets sucked back in.
James Heller: "You're cursed, Jack. Everything you touch, one way or another, winds up dead."
Speaking of touching and death, the main character of Pushing Daisies has the power to return the dead to life with a touch... but, he can't ever touch them again if he doesn't want them Killed Off for Real. Since he brought his true love back from the dead, this is sort of problematic...
Sam Winchester is one of a group of psychic children, some of whom have powers such as super strength, mind control, telekinesis, and the ability to electrocute people with a touch, he gets uncontrollable, painful visions of violent deaths. As one of the other psychics put it: "Dude, sucks."
Then there's both Sam and Dean being the vessels of both Lucifer and Michael respectively, meaning they're up against the combined powers of heaven and hell one way or another, and if they fold they will not only become meat puppets that will fight to the death against each other, but the entire world will end. At least demons won't kill Sam and angels won't kill Dean, though.
Dean's totally immune to Famine's hunger inducing powers which seems cool. Except that Famine points out it's due to the fact that Dean is so dead inside that no form of self-indulgence would fill the void.
John from New Amsterdam saves the life of an Indian woman, who in return grants him eternal life and youth until he finds his soul mate, who he will know by feeling it in his heart. Unfortunately, this means he suffers a heart attack that would normally be fatal once he gets near her (That is if Sara really is his soul mate. Unfortunately the series was cancelled before we found out for sure).
In Mighty Morphin' Power Rangers, Tommy Oliver joined the team as the Green Ranger and was considered the strongest ranger at first. However, due to manipulation of his powers by the villains, his powers were weakened and had to be constantly recharged.
Gwen Raiden has a massive dose of this. Sure, her ability to manipulate electricity makes her a fearsome weapon, gets her in a lot of places she could never otherwise go, and makes her oodles of money as a professional thief...but at the expense of even the smallest human intimacies and any semblance of a normal life. Plus the trauma of getting regularly struck by lightning and accidentally injuring and killing people who are actually nice to her.
Doyle also of Angel suffers crippling headaches due to his visions which are never very clear to begin with. Cordelia gets these later, and they nearly kill her. She becomes part demon to survive, rather than giving them up.
Echo's growing ability to retain the personalities uploaded into her during the two seasons of Dollhouse prove a decidedly mixed blessing emotionally, as shown in the series finale "Epitaph Two: Return." Subtly (or not-so-subtly) treated as a freak even by her allies because of her abilities, she cannot open herself up fully to anyone - not even Paul Ballard, her comrade-in-arms (during the apocalyptic future times) and would-be lover. As Paul succinctly puts it, "A hundred personalities, and you're the loneliest person I know."
In Early Edition, a TV series where a magical mysterious (but otherwise normal) cat delivers tomorrow's newspaper (a Chicago Sun Times) today, Gary Hobson views it as a curse rather than a blessing. He feels morally obliged to stop tomorrow's bad news, and similarly morally obliged to not to look up tomorrow's lottery numbers and stock tips. Subverted in one episode where a man from New York who gets tomorrow's newspaper from a parrot lives rich and happy, and has employees doing his work for him.
Some of the returnees in The 4400 are Blessed with Suck. While some get cool abilities like healing and seeing the future, others get lame powers (talking to plants) or horrible afflictions (Sherilyn Fenn's character grows toxic spores all over her body which kill everyone who comes near her.)
On a more mundane level, Bounty Hunter Aris Boch from the episode "Dead Man Switch" is a member of a Human Alien species (dubbed Ilempiri by the RPG) that has a biological quirk rendering them immune to Goa'uld infestation. The Goa'uld wiped most of them out because of it.
Tommy Dawkins from Big Wolf on Campus falls under this trope. Sure, being a werewolf does have its benefits... but not when one also has to deal with cravings for chicken, transformations for reasons OTHER than a full moon, and the fact that everyone else (save for his two best friends and a few monster allies) thinks he's an evil monster.
Being a companion of the Doctor. You get to see the universe in one of the most awesome time/space ships ever made! Meet historical people and aliens! And everywhere, you will be hunted, shot at, captured, insulted and tortured by historical people and aliens! No wonder most of them bail after a few seasons. Then you realize that most returning enemies like Daleks, Cybermen, the Master, Silurians, Nestene Consciousness are all still out there - and now you can't do anything. Enjoy your retirement!
It's been explored in the new series that You Can't Go Home Again often applies to ex-companions, too. Either normal life is a letdown after you've experienced the universe, or you know what's out there when others don't. However, a lot of companions Took a Level in Badass, so even if they can't travel time and space anymore, if you screw with present-day Earth, you'll have to answer to Rose, Mickey, Jack, Martha, Sarah Jane, and all the allies they've gathered during their absence from the series. Basically, run.
In Time Heist, Saibra's powers can't be shut off, which keeps her from ever touching people without assuming their form. Which means in her case, simple handshakes and sadly, intimacy, are to be avoided.
Captain Jack Harkness. He's immortal, giving him some wonderful Who Wants to Live Forever? issues as he watches every friend, lover and family member he has dies. And technically- he can die, he just comes back to life. Painfully. And while that's certainly useful, how would you like to be buried alive, suffocating to death and being pulled harshly back to life, for two-thousand years?
In Series 2, Owen Harper is killed, then resurrected by the resurrection glove. He is essentially a walking zombie. The main cause of his ensuing depression is, in his own words (paraphrased) "I can't sleep, drink or shag, and those are 3 of my favourite things." Any damage to his body is permanent, as well, since his injuries don't heal, so while a large cut on his hand doesn't bleed, the gaping wound will always be around to squick out everyone else.
Dead Last focuses on a band that is blessed with the ability to see ghosts thanks to a magical amulet, thus forcing them to accomplish their various Ghostly Goals. They are rarely ever happy to discover that the person whose existence they just acknowledged is actually a ghost.
In an episode of The Outer Limits (1995) called "The New Breed", Dr. Andy Groening was dying from cancer. When he learns that his brother-in-law has designed medical nanomachines which aren't yet ready for human testing, Andy injects them into his body to save himself. At first, his cancer disappears, his senses improve, and he becomes stronger and faster than the average man. The Suck comes when the nanomachines decide to make him invulnerable and make his body grow two more eyes, gills, and poisonous skin, turning him into a freak who is in constant pain from all the changes.
In the E4 series Misfits, a group of ASBO-serving teenagers develop superpowers after being caught in a freak lightning storm. Even the characters with awesome powers can't use them properly Every character in this show is Blessed With Suck to some degree, although that's undeniably the whole point.
Alisha has the unenviable power of inducing uncontrolled lust in anyone who comes into contact with her bare skin (regardless of gender). This leads to several moments where people who touch her will attempt to rape her, while spouting lines like "I'm going to own you, I'm going to shag you so hard" and "I'm going to rip off your clothes and piss on your tits".
Kelly can read minds, but has no filter between her own and her mouth, and having to hear what people think of her makes her even more paranoid, insecure and aggressive than she already was.
Simon initially can't control his invisibility, and once he can he's still something of a pervert and socially isolated - in fact, he gets worse because his power allows him to indulge his voyeuristic fantasies, and generally hide from reality. While that might sound pretty awesome, it sends him nuts in the end.
There's the hilariously awful like the "I'm a Jack Russell by night" dude, and the "I can make people go bald just by looking at them" girl.
It really says something when one of the most powerful and successful villains in the series had nothing more than telekinetic control of milk.
Parodied in a That Mitchell and Webb Look sketch, wherein a man bitterly angsts and curses about his supernatural ability... to levitate biscuits. It's a curse, apparently.
Stephen Baxter, from Russell T. Davies's The Second Coming, who suddenly discovers that he's the son of God. He does seem rather happy with it at the beginning (if unbalanced), being able to perform miracles and suddenly getting huge crowds of followers. However, it gets his best friend murdered, his father insane, and it turns out the whole plan is to have him killed by his lover so God can truly die and humans can take their fate in their own hands. He accepts it in the end, but ouch.
Anybody who possesses an Object in the miniseries The Lost Room. They can accomplish powerful and bizarre things, but anybody who comes to have one becomes more paranoid of losing it or becomes a Doom Magnet.
Bonnie, especially in Season 1. She's a witch, but apart from mind-whammying Damon, using any of her other abilities mainly seem to cause her to lose consciousness.
Elena's even worse. Her only power is looking like the original Petrova. The only time that her power is useful at all is when she manages to use her supernatural status to kill Alaric who was already dying and activate his ring.
All of the vampires. They possess superhuman abilities but they are cursed with the desire for blood.
And all supernatural folks (vampires, werewolves, witches) go to The Other Side when they die. Despite the name, it's really an in-between state; they just hang around, helpless to do anything but watch.
Being a vampire hunter often involves collecting a giant pile of magical tools, the most handy of which is a ring that literally just makes you immune to death. Becoming a Vampire Hunter, with the important capital letters? Well, now you're a supernatural creature, and in exchange for slightly elevated strength and reflexes pretty much none of your tools work anymore...
The Ocampa from Star Trek: Voyager are one of the most powerful races in its history. Eventually once they reach the peak of their powers they're capable of pyrokinesis, telepathy, shape-shifting, reality bending... unfortunately the key word there was eventually. Before they reach the very unlikely outcome of this great power, they have to cope with possibly one of the suckiest examples of anatomy and society in TV history. They can only naturally live nine years - which if you take into account their first year as children and last year as pensioners they really only have 7 years of decent life. They can only reproduce once in their entire lives using a complicated system of mating that involves attaching their hands together for a week. Ocampans apparently don't even produce litters - which for a race who can only reproduce once would be pretty damn essential. There are also only two known Ocampan societies - one underneath a completely barren desert planet in enemy territory and the other on a space station commanded by an insane creature that was one half responsible for turning that aforementioned planet into a barely habitable rock.
Star Trek: Deep Space Nine introduces us to a group of genetically-engineered humans. Many have impressively boosted intellects, if not special powers. Unfortunately, they're also rendered generally unstable, if not catatonic.
There's also the Vorta, once a timid ape-like species until one saved a changeling's life. The changeling promised to make the Vorta strong and powerful. Indeed, the Vorta are second only to the changelings in the Dominion and feared throughout the galaxy. But the founders eliminated their free will and programmed them to worship the founders unconditionally regardless of how horribly they're treated.
And from Star Trek: The Next Generation, a society which creates an empath designed to bond with and become the perfect mate for her husband. Sounds great, until it turns out that the guy she ended up bonding with is not the guy she has no choice but marry. Oops.
Ben from Carnivāle has the power to heal with a touch, but this power comes with a price, and naturally something else has to die in return. Growing up with an addled and emotionally abusive mother who told him he was "marked by the beast" didn't help, either. In addition, he suffers from terrifying prophetic visions which grow progressively worse as the series continues. Sofie and Justin are also blessed with their own versions of suck, as in this series no one who has avataric ability has it without a price.
In the Sanctuary episode Hero, Walter makes contact with a parasite that shields him like a suit and grants him extraordinary superhero powers, such as flight, super strength, bullet proof, and a host of other goodies. However, like most parasites, it is actually feeding off him and (unlike most parasites) slowly killing him.
An episode of The Golden Girls featured a Kavorka Man who professed to view his status this way — he's inexplicably sexually attractive and great in bed, but he's so ugly and boring that his relationships devolve into meaningless sex that any woman worth actually having eventually grows tired of. It's left open whether or not he's lying for sympathy, though.
In Kamen Rider Agito, people who begin to develop psychic powers are targeted and killed by monsters before they're even aware of their own powers, much less able to use them for anything practical. Also, the monsters will hunt down their families as well, just to be thorough.
In the Kamen Rider 555 universe, one has the chance to become an Orphenoch upon death. Orphenochs are almost immortal, have superhuman senses, the ability to turn into a stronger monster form at will and could be considered winners in the Super Power Lottery in multiple ways. The catch? Well, becoming an Orphenoch causes the Mega Corp. Smart Brain to force you into killing innocent people. If you refuse, they will send someone to kill you. And if you comply, you get the chance to be killed by the titular Kamen Rider 555 who protects said innocents. To top it all off, the sudden change into an Orphenoch renders a human body unstable, killing an Orphenoch over time anyways.
The Greeed from Kamen Rider OOO are revealed to be this. They have superhuman strength and agility, Immortality, along with some specialized Elemental Powers and the ability to create Monsters of the Week. The downside of being a Greeed is that none of their senses work really well, causing a permanent feeling of unfulfillment as their dulled senses keep them from reaching their desires.
Medic from Kamen Rider Drive has the ability to heal people, but in the process she copies their emotions. Since she works with the series' villains and thus heals people with a ton of negative emotions, she ends up being extremely bitchy. She was actually a good person at first, since she absorbed a woman's honest love and devotion to her dog, and at the end of the series she uses her dying breath to heal The Hero, copying his noble personality and dying as her true self.
Kahlan Amnell, the Mother Confessor from Legend of the Seeker, can tell when people are lying and can turn people into her slaves. However, when she takes over people, they have to stay that way until they or she DIES! Also, prior to her becoming Mother Confessor, Kaylan would faint when she used her power often in the MIDDLE OF BATTLE!
One episode of Beyond Belief: Fact or Fiction had a story about a guy who could kill people by painting their portrait. He used this "gift" to Mercy Kill terminally ill hospital patients, which seems like the only possible way it could be used, since it would be the most inefficient murder weapon ever. "Yes, just stand right there for a few more seconds while I get a better profile of your face..."
The Living Lie Detector who has a hard time socializing as he can easily tell if someone is lying to him.
The girl who has super senses also has extreme sensitivity, which makes passionately kissing someone for too long cause her body to overheat. Indeed, she's a neat freak because she can see bacteria, can't let anyone else touch or make her food, and is worried she'll never be able to have sex without having a sensory overload.
The girl with the Compelling Voice is mostly a moral version of this. If have the power to make people do whatever you want, it'll be really hard to not do whatever you want. It's treated like a drug addiction. Later episodes imply use of her powers is detrimental to her health as well.
Some of the objects in Friday the 13th: The Series. They and anyone who has ever owned/used them is thoroughly this trope. Some objects are solid evil, but some objects are capable of a good act: creating beautiful dance, healing, or mind reading, for a few. But there is an equally horrible act required: killing the dancers, needing to drop the disease or injury on another victim (tenfold), or mocking the user and bringing them to their death.
The title character of Merlin (2008) is the most powerful sorcerer that will ever live, but he's born in a time when magic's outlawed and his destiny is to go to Camelot, the heart of all anti-magic hatred, and to protect and teach Prince Arthur. Okay, not so bad. Then the show goes on, and he's constantly forced to Shoot the Dog, have virtually every good thing that happens to him yanked away, and is occasionally forced to confront the fact that he has no indication that all of his friends won't turn on him the instant he tells them. The guy has a bigger section on the Woobie page for Live-Action TV than Sam and Dean Winchester, the gods of woobiedom themselves. And if he backs out at any moment, destiny will fail and he will never be able to live in peace.
Jefferson from Once Upon a Time remembers the other world, but he is more than slightly mad since his real daughter doesn't and is happily living with another family, not knowing who he is. Most of his time spent in Storybrooke is trying to find a way to make a new one so he could escape from Storybrooke.
Chuck begins with the titular character getting the Intersect, a supercomputer full of government secrets, uploaded into his head. It allows him to speak foreign languages, use martial arts, and do whatever else the plot requires...not to mention bringing CIA agent Sarah Walker into his life. On the other hand, it puts him, his family, his friends, and his coworkers into near-constant danger from the many, many people who want to keep those government secrets hidden.
The trope is played even straighter in the fifth season, when Morgan and then Sarah get Intersects in their heads. It turns out that this version of the Intersect was deliberately tainted by their enemies, and it basically melts the brains of whoever has it. It turns Morgan into a massive jerk, alienating him from his friends and girlfriend, and it causes Sarah to lose her entire memory.
Jim Ellison from The Sentinel; super-enhanced senses sound really cool—but when paprika tastes hotter than cayenne, or a common cold medication knocks you flat on your. . .behind, not so much.
And that's not even the worst of it. Whenever he focuses too much on one particular sense, he has a chance of "zoning out", meaning he completely ignores all the other senses, which is demonstrated in the pilot when he is crossing the street and focuses on a flying frisbee, completely ignoring the honking truck moving towards him. Blair's primary job is to keep Jim from zoning out.
In a later episode, he undergoes a physical and has massive amounts of ear wax extracted, causing his hearing to jump Up to Eleven, to the point where he can't drown out the constant distracting noises at the precinct (somebody tapping a pencil is the equivalent of a drum to him), until Blair gives him a pair of white-noise earbuds.
It also serves as a problem in another episode where he has to testify as to the identity of a sniper, whom he saw on a dark street in a faraway building. Naturally, the defense lawyer destroys his credibility, and the bad guy goes free.
Koeschi on Grimm. You can heal with a touch or cause anyone who attacks you to die in a pretty horrible way. But you feel the pain of those whom you heal, and every time you use your healing, it weakens you, so if you use it too much too often you can drain yourself to death. Some of the other Wiesen have similar examples.
Axl doesn't really appreciate being the reincarnation of Odin. Not only are his powers sporadic and impossible to control, but if he dies before he finds Frigg, his entire family dies as well. And since they die of earthquakes, a lot of people will die with them.
Ty has it worse. He is Höðr, god of everything dark and cold. He can be pretty badass when he wants to be, and is actually one of the most dangerous and powerful gods. But he lowers the temperature of a room just by being in it (so no one wants to spend too much time with him), and he nearly kills his girlfriend with hypothermia when she leaves a finger on his exposed skin overnight. Pretty much every god he meets instantly expresses pity (or laughs in his face) when he tells them which god he is. He's the only one who isn't looking forward to getting his full powers back, since women aren't exactly going to be turned on by the idea of being with the god of winter.
Mike: Hey, I'm a winter god too. Ty: Of skiing! The fun bit of winter!
Clyde Bruckman has the power to see how people will die. It's vaguely useful for some tasks (especially since Bruckman works as an insurance salesman), but it makes him miserable. Not only is it often pretty disturbing, but he's never been able to prevent a death through his visions since nobody believes him. This leaves him with incredible guilt and depression. In the end, it gets too much for him to bear and he commits suicide rather than continue seeing death everywhere he went.
The Soul Eater is a man with the power to heal people. However he's also an Empathic Healer and he's used his healing powers so much, with so many different medical problems that he's been left as a deformed, barely functioning monster who's in constant agony. Similar to Bruckman, he's in so much pain that he ultimately chooses to use his power to revive Doggett after he's been fatally wounded and thus kill himself.
One episode of Fringe involved a man who was a reverse empath, meaning his emotions were projected and anyone near him would feel like what he was, he thus could spread happiness and joy and be someone you always would want to be around. Unfortunately the opposite was also true, and if he was ever sad or depressed couldn't seek comfort from anyone else without them feeling the same, this left him very lonely and socially isolated, and eventually suicidal, making it so that even going near him would make one suicidal as well and was quite dangerous.
A recurring character on the Canadian sketch show Comedy Inc. is a tantric sex master who has achieved the state of constant orgasm. It's quite clear from his attitude that it's not as good as it sounds.
Parodied in an episode of The Amanda Show with a group of teenagers with superpowers, including telekinesis, seeing the future...and, in Josh Peck's character Billy's case, "super rhyming." Not only was this a completely useless ability, he couldn't turn it off and was compelled to rhyme every time he opened his mouth (at one point, we see him groaning at his own Painful Rhyme, implying that he didn't want to say it). It also made him unable to have conversations: he couldn't talk until someone else did, because he had to choose a similar-sounding word for the sentence, and in some cases just babbled nonsense ("If you spill a soda, you have to...mop...fizz" or "I don't like lemons, because they're sours") because he couldn't find a meaningful rhyming word to contribute.
Farscape: Jool has a scream that can melt metal. Where it really sucks for her is when other characters injure or scare her in situations where they'd find it useful.
In Iron Fist (2017), the Hand are able to bestow the gift of Resurrective Immortality to their most loyal followers, so that if they die (without being decapitated or cremated) then they can come back to life. This does, however, have the downside of inflicting upon them a form of self-destructive curse, causing them to destroy that which they love the most in fits of sociopathic rage.
Game of Thrones has the Three-Eyed Raven. Able to project their consciousness across space and time to learn anything about the past, able to warg into any willing creature to control them and see through their eyes, and so overwhelmed by their newfound knowledge that their humanity and self are distant at best.
The western anthology series Dead Man's Gun ran on this trope as its premise. A variety of people in the Old West gain ownership of a rare and well-crafted revolver, which seemingly changes their fortunes overnight and instantly propels them to fame and fortune. However, the gun is also cursed, and leads nearly everyone who owns it to befall terrible consequences like Death by Irony, Death By Greed, a Fate Worse Than Death and more. There's a reason for this — it's revealed in the final episode of the series that the gun's original owner/creator is the Grim Reaper, who imbued the weapon with its cursed properties.
Lexx: Kai, last of the Brunnen-G, is an undead assassin who has existed for thousands of years, and has a variety of skills that make him nigh-invulnerable, such as being an Implacable Man who can heal from otherwise-fatal injuries like beheadihg or bisection, carrying a brace that he can control with his mind, and no-selling every attack anyone tries to inflict on him — and he fights on the heroes' side, to boot. The trade-off comes at a significant price, though — Kai runs on "protoblood", a finite resource produced by his creator (who is killed in the pilot episode and wouldn't bother to create more of it anyway), leaving him functionally inoperable without it. Several episodes are also written around Kai's deficiencies. Due to having his corpse stripped of carbon, he is much heavier than normal and has Super Drowning Skills. Other episodes force him to remain in cryosleep as the crew searches for alternate sources of protoblood, or the rest of the crew being in peril due to him repairing from an otherwise-fatal injury.