I'd say there's a difference between wanting to be and making a more interesting story.
I don't find roleplaying as what is effectively fantasy superman to be very interesting or fun.
Well, yeah, that's what I'm saying. Playing a Fantasy Superman is indeed completely uninteresting, which is what certain settings provide (*cough-Pathfinder-cough*)
But OTOH, playing an ordinary person who just happens to be stranded in a super-ordinary situation and yet makes it through despite everything seems illogical and dumb, and I don't understand the appeal of that either. I would certainly not want to play that.
Like
gurugeorge says, it's all mostly about escapism, and if I'm to "escape" to a different world far more dangerous than our own, I would want my character to be far superior to myself in many ways. Otherwise, I can't suspend my disbelief that this character would actually survive, let alone thrive, in such a world.
Many of the games on the codex's top 101(top 10 of those especially so) are about individuals who aren't particularly special.
Hoo, they are very much special. They start out weak, as is RPG tradition, but they rise above their rank far more quickly than they have any in-world reason to.
Bloodlines is probably the best example, as it's based on an actual PnP-game. A several-nights old Fledgling takes on a several-century old Vampire Elder in single combat and defeats him. That defies every single bit of logic in the cannon lore and can only be explained only by either "it's a video-game, who gives a shit" or "Caine is powering you up, ergo you're special". Either way, you're being given special treatment.
Planescape: Torment? - Can you
be any more special than someone who died over a thousand times and kept coming back?
And you're responsible for committing a crime so awful a hundred, a thousand lifetimes ago that it threatens to tear the very Planes asunder?
Fallout 1/2 - OK, you're kind of a nobody in 1, but already in 2, you're the descendant of your famous progenitor from 1, making you pretty special.
Arcanum? - You're a reincarnation of a god, or so it is believed.
Deus Ex? - You're the specially produced, most expensive bio-weapon ever put together by any international organization, ever. Hell yeah, you're motherfucking special.
Etc. etc.