I’m not going to get into the Tyranny of Fun argument; it’s been done to death, undeath, redeath, and nonliving states we don’t even have concepts of. Instead, I’m looking at the idea of people—even protagonists—not always succeeding from a more dramatic standpoint.
First off, failure is generally better for characterization than success, particularly when the character is accustomed to succeeding more often than not. After all, without the expectation of failure, success just seems like more of the same. (Success against all odds is another story—I’ll get back to that later.) But when people fail, they usually react strongly to their failure. Perhaps they attempt to assign blame for it to something or someone; maybe they look at their own failings and try to figure out what weaknesses to shore up. They might respond with fury, or resignation, or just take it in stride. But whichever they do, that’s likelier to say something about them than just watching them win at everything they try. And then, isn’t it that much more rewarding for them to come back to the problem that bested them, with better skills or a new approach, and succeed?
By the same token, too much success in a row can lead to complacency and a sense of entitlement. People who make a habit of winning, particularly those whom the world seems to smile on, might take for granted their victories—in character, this is only interesting if it’s the prelude to a fall, and out of character it can ruin suspense or tension, as the outcome is rarely in doubt. (It’s even worse in games when they come into the game with this kind of attitude, particularly when that’s not the feel you’re looking for.) But if every now and then things don’t all go right, it doesn’t start feeling like they always will.
Another thing to keep in mind is that people who realize that failure is a possibility are likelier to know when to run away. This is more of a gamer’s issue than an author’s, but can be applied to both. Imagine a group being sent up against something they can’t possibly handle. There’s a chance they’ll realize that, but it’s just as likely, if they’re used to nothing but winning, that they’ll figure it’s just another fight they’re “supposed” to win—perhaps we just haven’t found the right gimmick yet!—and thus keep going even when the only way to live is to cut and run. (It’s hard to tell what’s worse: how insufferable they get if they win anyway, or how much they kvetch if they lose.)
A side bonus to creating situations in which failure is probable is that those who succeed anyway are going to be that much more excited by it. To what might they ascribe their success? If it’s not in one of their normal strengths, might they try to pursue that as a new emphasis? How do they react to the world around them being equally surprised that they made it through? (One of my best characters was almost entirely a result of processes like these.)
A last note on why the main characters shouldn’t always just succeed—in my opinion, winning all the time gets boring.