First of all, COD does not provide realistic power - just think about how many kills the player-character gets in comparision with his team-mates.
OT: While I think I can enjoy games with any of these power levels (and like others have said, it depends largely on the setting), I find that I enjoy games with uber-powered characters more often. For one thing, in any game where there's a great evil to conquor, or a challanging quest to complete, there has to be an explanation for why your character is the one to take it on, and making him the most powerful or best equiped character will almost always provide this. Admitedly, this is a slightly lazy explanation, but I rather have that then no explanation.
Also, making the character more powered gives him more freedom, which one could argue is the very essence of gaming. Leting the character do more stuff reneforces the idea that the player is in full controll; again, there may be less lazy ways to do this than giving him/her more powers, but sadly your just not going to get that from a lot of games.
So, while it may not be the perfect option, I find that an powerful character is usually a benefit to a game. Realistic is better for sims, weak is better for atmoshphereic games, increasing power can often compliment a strong story, but for your typical action game, the less limits there are the better.