I think we all agree that a game without any chance of failure would be extremely boring. If you always succeed things tend to get uninteresting after a while. But on the other hand repeated failure can be very frustrating. And I think we all know situations where a failed roll ruined the whole gaming session.
Especially in investigative scenarios failing certain skill rolls can be a show-stopper. When you don’t find the necessary clues you can’t solve the mystery. Of course, a good GM might find ways around this, by offering alternative ways for the players to get their hands on the needed clues or he may fudge a few dice rolls, to get them closer to their goals, but that’s not always possible nor desired. Robin Laws solved a lot of these problems with his GUMSHOE system, in which investigative abilities always deliver the wanted result. When there’s a clue and you have the right skill, you find it. While this works perfectly in Trail of Cthulhu and other GUMSHOE games, I wouldn’t want to run every game using the GUMSHOE rules.
The easiest way to overcome the problem with failure is by always letting the players succeed but if the roll failed add some complications. Imagine a character is trying to climb a wall. He makes a Climb skill check and fails. In most games that means the character slipped, fell and takes some falling damage. Ouch. I still remember when I lost a character fifteen minutes into the game. That’s definitely no fun! An alternative is to let the character succeed at climbing the wall but add some complications. The climb may simply take longer, or the character may be extremely exhausted when he reaches the top. Both outcomes may or may not influence the scene(s) after the failed climb roll.
Another example: a character wants to open a locked safe to get to some important documents inside (which are needed to advance the story). He fails his roll. Instead of letting the action fail completely, the GM could decide that the character succeeded at opening the safe but triggered an alarm. This method allows the GM to keep the story running without making it too easy or too uninteresting for the players. Failing a skill roll does not mean failure but just adds complications which make things more interesting.
A GM who wants to give more narrative control to the players may even consider allowing the players to describe how they failed or what complications they now have to face. Of course the GM should always have the right to veto, since this can be easily abused. But if done the right way this will not only make your game more interesting but also empower your players by giving them more control over their characters actions.