In Kingdom Hearts: Birth by Sleep, Aqua is by far the hero. She is the only one to achieve the rank of Keyblade Master, never succumbs to darkness, puts her friends above herself, and always fights to help others above all else.
In Sly Cooper, Sly is the hero even though he's a thief. Even though he's considered a criminal because he steals, he only steals to save the world from criminals who actually are evil and have devious intentions.
Master Chief Petty Officer John 117 of the SPARTAN IIs. Not the strongest, fastest, smartest of the Spartans, or the best at anything at all, except in leading the others and always getting the job done. He is so unbelievably Mario in comparison that even though there is at least one SPARTAN that can do something better than him, he wins everything because there is nothing he is bad at. Nothing. And he knows it. That's why he was made The Leader of the rest.
Although Cortana says he has one thing the rest didn't: LUCK.
Another thing he is the best at is being the Bravest with a capital B, as said by Dr. Halsey herself. Spartans are by definition extremely brave and invulnerable (latter as symbolism, but proven with the best record of survival rate per engagement). However John takes it to the pure insane combined with his luck and courage to do the pure impossible even by Spartan standards.
Although there's the possibility of it being too obvious to be worth listing here, the majority of the main characters in every Dragon Quest game serve as the Hero, to the point that in Dragon Quest VIII the hero's default name is Hero. Nearly all of them have balanced abilities and use (or have the option of using) swords, and many of them have lightning and/or fire in their selection of attack magic. One of the only exceptions is the Hero from Dragon Quest II, who learns no magic at all and serves as the physical fighter of the party.
Another DQ Hero-Subversion, in V the hero plays the role as more the party's priest, and his son is the legendary hero. Ironically though the son of the priest hero from V can't function without his father's help considering that the son is inexperienced, and he doesn't know what he's doing.
Devlin McCormack from The Orion Conspiracy. Unlike a lot of games out there, he is different. How so? For starters, he is a middle-aged guy who has a number of issues. He was not a good father or a good husband. He does feel bad about it. His son and wife are both dead. He fought as a soldier in the Corporation War. However, the game demonstrates that he seems to prefer using his brains and guile rather than a gun and physical combat. He also engages in lying, petty larceny, blackmail and some Shoot the Dog moments. The Chick is also not attracted to him. With all that said, he does lead the charge more than once in the game.
Rosalyn from Okage is another case of the hero not being the player character, generally being a superb Hero's Guild Member and overshadowing Ari. But then, everyone does that last bit.
Played straight in most of Fallout 3, but subverted in four of the five add-ons (Broken Steel being a continuation of the main storyline, very heroic, and something of an Author's Saving Throw for the original ending). The main game revolves around one man's dream to bring free, radiation-free water to the Wasteland, and how his child either achieves or subverts that dream.
Operation Anchorage requires the player to aid one of the least sympathetic non-psychopathic factions in the game (who only exist as a faction because they broke away from a group that decided it was more important to protect innocents from 8-foot-tall genocidal mutants than to hoard technology).
The Pitt forces the player to choose between allowing miserable slavery to continue in the name of rebuilding the only working steel mill on the east coast and finding a cure one of the most devastating mutations in the Wasteland, or freeing the slaves by replacing a Reasonable Authority Figure with his treacherous lieutenant, who then plans to use his former Lord's infant child to find a cure as fast as possible, her health be damned. Also, it is implied that choosing the latter will allow the steel mill to rot, a significant setback for rebuilding.
Point Lookout has the Lone Wanderer caught between a Bad Ass and a Chessmaster. While none of his actions are decidedly heroic or villainous, he gets outsmarted by brain-damaged drug addicts and (depending on what side quests he follows), a 200-year-old death trap. Both these events paint the hero as more of a gullible imcompetent than anything else.
Mothership Zeta is mostly a fight for survival, ending with the player willingly destroying a craft capable of traveling to other planets. That, you know, aren't ruined.
In the Video Game/Fable series, Heroes are a classification of people in Albion who possess the disciplines of Strength, Skill, or Will, and as such have extraordinary fighting and magical abilities. The player character in each game has control over all three disciplines, and can either play this trope straight or horribly invert it.