I couldn't agree less.
I prefer to play with game masters who let the dice fall where they may - if s/he is concerned about not killing a character during a particular encounter, then don't design an encounter where death is on the line should the player come up with a string of natural ones. This "dramatic moment" or "big final fight" stuff leaves me cold - design the encounters and leave the storytelling to the players after the battle is fought, the dragon slain, the princess saved, the treasure vault looted...and if my character falls in a pit and gets impaled on a half-dozen spears just outside the dragon's lair, then that is (1) the luck of the dice and (2) your poor choice to put a lethal trap at that spot.It sounds more like the story that Keifer113 makes, and we're not allowed to mess with it by dying at an "inopportune" moment.Miss out on what exactly? "Oh, I didn't really mean for you to get killed crossing that raging river - that was just to heighten the drama." "Really? It was already plenty dramatic for me when I had to make the Swim checks or die."
As you say, the fun is in facing the challenge - if the challenge isn't real (that is, lacks meaningful consequences), then the fun is significantly lessened.
Oh, and if a player lavishes so much attention on a character that s/he isn't willing to see the character die in the first round of the first encounter, then that character should take up a quiet life behind high town walls, maybe doing needlepoint or haberdashery. Adventuring is dangerous - get rich, or die trying.