As a kid, my father listened, almost exclusively, to novelty songs and film soundtracks. As a result, it gave me an appreciation for the power and necessity of film music: a good score can subtly color a scene, giving it tension, emotion, or even joy, with the theme as a centerpiece.
So when your theme has all the appeal and joy of a kick to the nuts, you've got a problem. Here are the five worst themes across all of SF TV.
"OK, OK, I've got it! Dananananananananana WONDER WOMAN! Danananananana!"
"That sounds derivative, Bob."
"Screw it, I'll make it work."
Composed by Mike Post, who you probably know better as the guy who scored "NYPD Blue" and created the "doink doink" sound for "Law and Order". In fact, he also composed the theme to "The Greatest American Hero", which was actually a charting hit.
This we're assuming he composed for malls. He's so embarrassed about it, he's been going around asking YouTube to disable embeds: this was the best quality embeddable copy I could find. Trust me, it doesn't matter it's from a VHS. Even in high fidelity, it's still music to shop by.
Another old shame they're trying to keep from being embedded outside of YouTube; this is a cover version, believe it or not, and it's actually better than the original.
This is really a good demonstration of how one bad decision ruins a score. If it weren't for those stupid "pew pew" noises, people probably wouldn't even remember this intro.
There's nothing inherently wrong with using a pop song for your TV show's opening. "Charmed" used an OK Smiths cover, "Chuck" used "Short Skirt/Long Jacket" by Cake, "The Dead Zone" used Jeff Buckley...the point is, they actually picked something at least somewhat thematically appropriate, instead of basing their pick off market research and the lyrical hook.
Then again, it's not like they couldn't, oh, I don't know, have used John Williams' classic theme, because it's not like Warner Brothers owns that or anything.
Star Trek themes have a tortured history. If you've never heard Gene Roddenberry's lyrics to the original theme by Alexander Courage sung (something Roddenberry did to collect a royalty check), spare yourself the pain. "The Next Generation"'s theme is actually from "Star Trek: The Motion Picture", something nobody bothered to even tell Jerry Goldsmith, the composer, they were doing. "Voyager" uses a vocoder, for God's sake; Goldsmith might as well have titled it "Oh, You're Bothering to Pay Me This Time? How Nice Of You."
The movies, by the way, have it even worse in some respects: for example, James Horner pretty relentlessly recycled his score for "The Wrath of Khan" for a solid decade. It and "Aliens" are essentially the same soundtrack.
Still, this is terrible, and we blame the studio: they said, explicitly, they wanted a top ten hit. It didn't happen. We can't imagine why!