It varies. The original ending of Fallout 3...
There are some games where it's definitely thematically or dramatically appropriate. I think the first game I saw in which this happened, many years ago, was an adventure game called Dreamweb. In that, it was kind of effective- essentially a cold, final moment of clarity that the forces that have sent you on the game's quest were using you, and didn't really regard you as anything more than a tool.
It's definitely not a choice that should be made lightly, though, because if you flop that one and the death comes off as trivial or contrived- hoo, boy, are you going to receive some rage. And rightly so, frankly. In nearly every game in which the player character serves as a kind of player-surrogate, you spend most of the game expanding and developing that character's abilities and- even more so- keeping them alive. To snatch the controller out of the player's hands when it's most important because the writer suddenly decided that "pathos" was the direction they wanted for what you've been encouraged up to that point to think of as "your" character- that can easily be a mistake.
...Mostly pissed me off because the game had the nerve to call me "selfish" after a whole game's worth of good karma for taking a few seconds to figure out the code. (Yes, perhaps it was boneheaded that it took me that few extra seconds, but still! Sheesh! It's not enough that I throw myself to the lions, I have to do so at a sprint?!)
There are some games where it's definitely thematically or dramatically appropriate. I think the first game I saw in which this happened, many years ago, was an adventure game called Dreamweb. In that, it was kind of effective- essentially a cold, final moment of clarity that the forces that have sent you on the game's quest were using you, and didn't really regard you as anything more than a tool.
It's definitely not a choice that should be made lightly, though, because if you flop that one and the death comes off as trivial or contrived- hoo, boy, are you going to receive some rage. And rightly so, frankly. In nearly every game in which the player character serves as a kind of player-surrogate, you spend most of the game expanding and developing that character's abilities and- even more so- keeping them alive. To snatch the controller out of the player's hands when it's most important because the writer suddenly decided that "pathos" was the direction they wanted for what you've been encouraged up to that point to think of as "your" character- that can easily be a mistake.