I do have kind of a weakness for 'loose cannon' players, because they tend to keep things moving. But by this I mean, at worst, someone who occasionally does things worthy of the comic relief in a Hollywood action movie. (I wouldn't want anyone to intentionally endanger the rest of the party, but occasional incompetance can be endearing.)
I've found that a little troupe style play helps with this kind of thing. In a session I ran recently, I ran a flashback for each PC, and handed out supporting NPCs for each bit. One of my players who was running a hyper-competent sniper type chose a true screw-up as their throwaway character, made life difficult for the rest of the group, and was generally hilarious. (It was a military scenario, and this character was modeled after the "Spud" (from Trainspotting) character from _Black Hawk Down_.) Not only did it lighten the mood, but I think it let him get something out of his system and increased his enjoyment of his rather more straight-laced main character.