Eric from Blizzard Storm definitely fits this trope, as he is able to go up against Angels and Demons and win.
BlackDragon6's Ranma ˝ story Guardian features a character named Ken "Snake" Yakata, a Jewish gun nut who is routinely billed as the most deadly fighter in the Department of Abnormal Phenomenae Containment with the sole exception of Ranma. This is in spite of the fact his other officers are: a powerful psychic psycho capable of cutting the heads off powerful vampires, a superhuman capable of going toe to toe with cyborgs and the worst boss enemies in the Resident Evil series, and a bloody Zergling. All Snake has is a shotgun. And lots and lots of firepower. (The man somehow have a working ICBM silo in his house)
To further emphasize the extent of this, it should be mentioned that Snake terrifies every single one of his fellow officers, save Ranma. To an extent, anyway. They know he can't shoot them and thus they feel somewhat secure around him, but around his evil clones they are terrified.
In the spin off Millenium, during one encounter, the DAPC's captain have a threat index of 5 while the Sailor Senshi got a 4 .Snake? A solid 10.
Balian of Ibelin in The Chance Encounter series. While he has been rendered immortal, this only seems to prevent him falling ill or indeed ageing. He also fights alongside Achilles, Legolas and Will Turner (during his "undead captain of the Flying Dutchman" period) and holds his own easily. When he is briefly turned evil by dark magic, he even forces LEGOLAS into retreat. He also takes on many of King Richard I's soldiers at once and wins, with the King's spymaster not sure if the King (who was a noted warrior himself) would survive a head to head confrontation. Oh yes, and he wades through HELL ITSELF to save his dead-by-suicide wife, taking on Satan and...mostly getting the crap beaten out of him actually, but he stood up to the ultimate evil and survived!
Author Avatar Art of the Mass Effect self-insert Mass Vexations eventually becomes a good Soldier-class unit within Shepard's squad. He never got any of the genetic implants that most soldier-class units get in the Mass Effect universe. He also Took a Level in Badass considering he had never fired a gun in his life before entering the Mass Effect universe.
In the Transformers fic, They Just Don't Care Anymore, the character Alien spends a lot of time being criticized as a minor character who won't get to do anything important and will inevitably be the first to die when a horde of undead attacks... until a horde of undead actually attacks. In a Moment Of Awesome, she announces that she's sick of being called a minor character, whips out a pair of kukris and single handedly takes down everything in her path, after which she proceeds to throw a guy into a quarry because he annoyed her.
Poké Wars has Samurai, a Pokémon trainer who lost his Pokémon early on in the war. Yet, he is more than capable of holding his own in a fight against them with only his katana.
Lt. Surge also counts. He was able to hold off a group of Electivire and Scizor with only a combat knife.
Don't forget Tracy. He held off pokemon from Oak's ranch with nothing but a lead pipe and sleep deprivation.
Gwenith Cousland and Theron Mahariel in the Dragon Age fanfiction Dragon Age: The Crown of Thorns qualify, although the latter's very sharp hearing could be seen as a Charles Atlas Superpower in its own right. Of course, seeing as how everyone else, especially Kallian Tabris, either came with or gained some power or another over the course of the story, it is unclear if this status will last for the human noble and Dalish. Leliana and Zevran still fit perfectly though,
A few in Spider-Man Evolution, most notably Daredevil and Moon Knight, both being able to kick Spidey and Black Cat's butts without the use of powers. Similarly, both Kraven and Silvermane, both completely normal people in this story (the latter being past his prime as it is) and are capable of tossing Spidey around when they faced him the first time around. Both MJ and Detective George Stacy are also notable during Chameleon's hotage takeover, MJ basically goes around knocking out mooks with a frying pan and ends up saving the day, while George Stacy beats up an armed mook who threatened his daughter without any weapon himself.
Alex from Adventure Time fanfic, Alex. She's a nerdy human girl who acts as Marceline's opponent in the election for the position of Vampire Queen. Despite that, she can take on the Vampire Queen herself with her laser pistol and lightsaber, as well as her blocking skills.
The Pony POV Series has several, but the one that stands out the best is Patch from the 7 Dreams/Nightmares Gaiden story. After she Took a Level in Badass in her quest for the Shards of the Rainbow of Light, she becomes an Action Mare capable of taking on a full grown dragon and freaking Grogar by herself and winning. The only 'superpower' she has is an extremely slow aging process caused by the Shards. Besides that she uses guns, and when those die out, a sword.
Hermione's parents, of all people in Knowledge is Power. They may be Muggles, but they're deadly with firearms.
In The World of the Creatures, Brendan Anderson is just your average, 19 year old English Major. And yet, through his combination of heart and smarts, he's able to stand toe to toe with the likes of Buffy Summers, Batman, and the Doctor.
Equestrias First Human: Connor, the human who ends up in Equestria is beat up by a pair of traitorous royal guards, and still has the strength to take down a ragtag group of rebels with makeshift weapons alongside Spike, and then he overpowers and defeats Hellfire-the Big Bad and a deranged unicorn stallion powerful enough to slay a dragon- singlehandly
In the sequels, he still qualifies as an Earth Pony, an example being able to survive a battle with Queen Chrysalis who at that point was strong enough to defeat Princess Celestia.
The protagonist of Middle Of Nowhere arrives in Fereldan as a middle-aged and dinged up Army sergeant. He relies on his wits and only just survives his first encounter with some live darkspawn, but levels up over the course of the story. Still, he does lament not being "an all-powerful mage."