Good thread. I'm having to put a little thought into it.
My first impulse is that I'd like to serve under Captain Sisko aboard a starship, not necessarily DS9. He seems to be a good blend of a lot of the other command personalities in Trek. He's a man of action like Kirk, a thinker like Picard, and as we saw, not beyond delving into the grey areas when he has to for the greater good, like Janeway. Based on my own time as a Navy officer and having served under several CO's (good and bad), Sisko's character seems like a CO I would mesh pretty well with given my own personality and leadership style.
I'd find Picard too touchy-feely and cerebral, Janeway far too temperamental and inconsistent, and Archer just plain incompetent. Realistically speaking, from about S4 of TNG onward, Picard would have made a better admiral than a captain.
I think behind Sisko, my other preferences would be Kirk and Riker, probably in no particular order. Riker always seemed like a TNG version of Kirk anyway. Those guys were great leaders, and would tend to always inspire my best efforts even if their demands were a bit chafing.
Since we're taking captains, I shouldn't leave out my opinion of the most notorious of them all: Jellicoe. A lot of old school Navy captains were cut from that mold. It gets the job done, but leaves those under his command perpetually deflated and feeling undervalued. Retention under commanders like Jellicoe always suffers, but the efficiency numbers are usually good. Ironically, few of them make flag rank in the real world. Every admiral I ever met (with one exception) was a politician and a 'people person.'