Anti-Heroes

When you create your main character and you want to avoid eye rolling you'll probably want to refrain from making your character too Mary-Sue-ish (see my Article Is Mary-Sue always to be avoided?).
Every "How to design a character" guide will tell you to give your main characters some faults. "It makes us identify better with the character" they say. And so you try to make up some kind of fault that you slap on your character without getting in the way of his overall awesomeness.

But I think you're doing something wrong here. The fault you give your character should not just be overlooked. Your characters fault should be what defines him. In a really good story, the main character's weakness is what makes him appealing. If you can pull that off then you're on your way to creating a good character.

Look at Johnny Depp as Captain Jack Sparrow for example. That character is incredibly quirky and unbelievably flamboyant. He's doesn't seem that courageous and he also seems rather selfish. But would he be more appealing if he wasn't? No, we like him because he's such a bad role model.
Another example of a very similar character is Robert Downey Jr as Sherlock Holmes. Drinks a lot, very quirky.

One of my favorite characters from a tv show is Dr. Cox from Scrubs. He has a huge problem controlling his anger, he drinks, he's mean and everyone is afraid of him. But we love to hear his elaborate insults. They're hilarious.

One of my favorite characters from a manga/anime is Asuka from Neon Genesis Evangelion. She's also an important example because she's different from the other characters as in her fault is not only a device to make people laugh. She's a bitch. In funny and serious situations alike. Of course we like her because she's a badass but wouldn't she be less of a badass if she was nicer?
Same thing goes for another one of my favorites Vegeta from Dragonball. He's introduced as a sociopathic murderer who kills his long-time companion Nappa at the first sign of weakness. And even after turning "good" he is anything but. His arrogance is probably his most notable attribute and a lot of the other characters are repulsed by his personality. But we ain't. We love him for being such a bad person and we can't get enough of such bad-assery.

I'm not saying you should copy these characters. These are just examples to show you that negative attributes can work in a character's favor and in my experience (and my personal opinion of course) those are the better characters.

So don't make people love your character for what they have to offer but rather for what they lack.