Of course we WANT them to succeed, but where is the dramatic tension if they ALWAYS succeed? Every time? Why should I care if Janeway is infected with this weeks space virus if I know, for a FACT, that she will be cured and everything will be fine? Where is the tension when Sisko is crashed on a desert planet on the other side of the wormhole with no communicator, no water, and no one around if it is a certainty that at the end the Defiant will find him and whisk him off to DS9? What does it matter if Archer is facing down 100 Xindi warships if there is no question at all that he and his crew will safely get away with barely a scratch? Every episode? For year after year, series after series? The days of "Geez Louise, how are the fellas gonna get out of their scrape THIS week?" are long gone.
Now, I'm not saying crew should be killed willy nilly at Walking Dead/Game of Thrones levels of carnage, but maybe once every season or even every two seasons, having someone die or a radical status quo change for them is good tv. Look at Wesley's whole arc on Angel, Wash in Serenity (a movie but still well done), Etta in season 5 of Fringe, Teri Bauer in 24, Terry Crowley in the very first episode of the Shield (with honorable mention to Lem), Rita in Dexter.
Despite what 50 years of Star Trek have tried to tell us, space is not safe! It is the most dangerous environment to work in where one wrong move gets you and possibly your crew violently dead. Throw in antagonistic and powerful aliens, technology run amuck, alien plagues, mysterious gaseous anomalies, and tribbles? It stretches my credibility to the breaking point to think we are not going to lose a few someones along the way.