Yesterday I mentioned my preference for anti-heroes, unlikely heroes, and generally anything other than the standard intentionally-saving-the-world type heroes.
I'm no fan of King Arthur or Gandalf, and Superman's always left me dry. I'll take the Punisher or Samwise or Cugel, (especially Cugel), or Elric.
I think Elric's really the best archetype for my style of adventuring anyways, since ol' Sam knows that he's trying to save the world despite him being the least seemingly-capable person to do so, and the Punisher's sort of trying to at least make the world a better place (although the violence makes him a bit morally questionable, there's no way you can say that he isn't at least trying). And Cugel, as we all should know, is kind of an idiot and doesn't even manage to save himself. Or get anything accomplished- he generally manages to get even further behind than he was before he started on whatever ill-attempted methods he'd been trying.
But Elric manages to make the world a better place, and not because it's the "right thing to do", since after all, sometimes it isn't. But in pursuit of coinage, power, or knowledge, he at least manages to get some stuff done. His adventures are always interesting and varied, because he's after something. The world doesn't need to be saved, and it's honestly more interesting for it.
I don't mean to tread on anybody's toes, but the least interesting thing about a danger to the entire world is the danger itself. For a good example, look at Zombie Apocalypse movies. The reason for the zombies is almost always left unexplained, and none of the characters are particularly interested in finding out. It's not even relevant. They're not trying to save the world, or uncover the mystery behind this enormous earth-crippling catastrophe. Nobody cares, and the movies that are about the actual disaster tend to be uninteresting (I am Legend comes to mind; the most boring parts of the movie are the ones with boring psuedo-scientific babble, and the most interesting ones are the parts where he's dealing with the fact that the entire effin' earth is dead, except for him)
And so it is with fantasy novels and, by extension, games. The interesting part about the Demon Lord Psodi'dsfzx wrenching the world's sky with a hellish fire-portal isn't that now you get to march over with shining armor and weapons so magical that they sizzle and fart with magical power, it's the fact that life, inevitably, goes on. How does the world change when demons irregularly rain from the sky? Do people live underground? Do they live in giant groups for mutual protection of their livestocks and livelihoods? Has humanity been ground into dust and serve the demons with their lives like human cattle? That's the interesting part, not some big silly flashy battle.
For a good comparion, we'll talk about everybody's favorite book, the Lord of the Rings. I maintain that it is substantially less interesting than the humble Hobbit, for all the reasoning I already went over in the paragraphs above. It's focused on a band of adventurers who are going to slay a dragon not because it's a terror (it isn't), but because a small group of dwarves wants their mountain back. In the process, they manage to find the One Ring, burn down a lake town, slay some goblins, meet elves, get captured and then break out of said elves' jails, and have a giant pointless battle. Sounds like a hell of an adventure to me.