The first game I ever had a character die on was The Greatest Generation (it was the first non-consent game I ever really got into). I had lots of characters die there, just because of the nature of how the combat code worked, and after awhile I kind of got a kick out of it, in a macabre way. My poor bastard soldiers got a reputation for turn-over after awhile, both because I was very active at times (and able to make lots of battle scenes) and because I actually did shit in scenes (and 'doing shit' is either glorious or ruinous in some high-risk situations). I also did a lot of soft RP, so my characters were pretty well-developed by the time they died, and a few deaths seemed to actually impact other PCs and created a lot of ripple RP I loved to read (reading about your dead characters is the best kind of mention porn).
I was cool with losing my characters on TGG (and most of the players who stuck around for any length of time were), I think, because the headwiz was very, very upfront about the level of risk in combat scenes, and I always felt like things were fair. This is all I ask out of a GM. Be clear about the chance of a character dying, even if it's remote, and I'll happily roll with it. This is one of those things that requires a high level of trust, though, or else it's not fun, and trust is a frustratingly tricky commodity between players and GMs on MU* settings.
I don't think characters need to die for RP to be worthwhile, or to feel like there's a certain amount of risk in a scene (though I do, always, want stupid, stupid IC actions to have consequences somehow, even if it's just be a mission failing or an injury that the PC has to deal with). And I'm fine with players don't want to go there, as long as what they actually want isn't "I always want to win because winning is the only fun thing." But I do think it can make RP and stories more worthwhile, in the right situations.