Depends on the context. If his/her death accomplished something meaningful that would carry on into future generations or they were a sacrifice to destroy a legendary enemy then sure, let them go out guns blazing. Unless there's a sequel it won't matter much anyway- the reason you built up your character was so that they had the power and loyal comrades to accomplish the final challenges at the end of the game, the ones where you stop the greatest evil phenomenon in their world at the cost of your own life.
(SPOILERS)
My favourite example of it being done well is Zero from the Mega Man series. After several false starts he finally ends it completely free of the curse of his insane creator, as well as killing another villain who was every bit as despicable as Dr.Wily, and bringing about a new era of peace between robots and humans.
One I didn't enjoy so much was Kratos in God of War, because it didn't accomplish anything. Apparently he just couldn't stand the pain of his past to live on after getting his revenge, despite having born it for years after and being fully aware that humanity is in for a horrible few centuries thanks to his acts. Then again, he is miles away from a traditional hero and I would never call him a coward.
Everyone will be talking about the Mass Effect 3 ending here, but more generally I don't think the hero dying in the ending has become a common enough trend to be detrimental to the experience, particularly if it's foreshadowed well as in Persona 3.