Regarding Greek heroes and such, I think the difference is that some people want to play games where we find out whether the characters manage to become such heroes, whilst others want to play games where the characters destined to greatness.
I want to play characters who already are going to do great (and possibly terrible) things, but who still have uncertain futures within that space. I want to find out
what those great-and/or-terrible things will be, what costs will be paid, what triumphs and sufferings these exciting people will experience. I don't want "destined to greatness"--I want destined to
interestingness. The fake "Chinese" curse, "may you live in interesting times", or the two additional, allegedly worse, "curses" that Terry Pratchett added to make a trilogy: "may you come to the attention of those in authority" and "may the gods give you everything you ask for."
There's a reason I keep making comparisons to things like
Babylon 5. We don't watch TV shows about random nobodies with lives of quiet desperation doing mundane drudgery until they die for complicated but uninteresting reasons. We watch for characters like Londo and G'kar, people who start out as jerks you love to hate, and who end with the audience in tears, wishing there were some other way. We don't read novels about Seamus Finnigan and Lavender Brown, even though we are meant to understand that they are just as much real people in the world of Harry Potter as Harry, Ron, and Hermione. We read to hear about The Boy Who Lived and what other cool things he gets up to--and then we
stop paying attention as soon as he settles into a relatively pedestrian, if still adventurous, job at the Ministry as an Auror.
D&D arose from two roots. The first, as we all know, was wargaming. But the second--the thing that was inserted
into wargaming--was telling the story of a Fighting-Man or a Magic-User, and clearly inspired directly by characters like Fafhrd, Conan, Doc Savage, and Aragorn. Many of the early influences were gritty and pulpy, but I don't think there's really any room to argue that Fafhrd or Conan is anything
but a classical hero doing classical hero things and being larger-than-life. Nobody mistakes Conan for a random Joe Shmoe.
Now, not one word of that means people can't enjoy playing Random Joe Shmoe, nor enjoy the process of discovering
whether a character will ever amount to anything exciting, or will die in obscurity. Personally, I see the latter as not just dying in obscurity, but having an 80%-90% chance of dying for stupid, pointless, depressing reasons in the middle of nowhere whiel accomplishing absolutely nothing and being instantly forgotten. I find that experience soul-crushing. That's why I avoid it like the plague.