The game has to have a spartan simplicity in its controls for a stick to work. Any game, intended for a pad, using more than four buttons is just not going to work. It also can't require analog movement.
Example. I bought Gradius 5 and Alien Hominid on Tuesday. Gradius uses a classic 3 button contro system, so is easily adapted to most any stick. Alien Hominid uses a 6 button method, and add to that the shoulder button are intenede for left-right rolling. Alien Hominid just would not work well with a stick, unless you negated the rolling.
I also remember trying to play Contra SS with a stick. That straight doesn't work.
NES, Genny, and TG-16 emu use works, but SNES emu gets iffy because of the shoulder buttons. Contra 3 would have been fine if not for those overhead sections.
Other games I've used a stick for.
Crusader: No Remorse - not real good
Unreal 2K3 - Trackball + X-Arcade
System Shock 2 - Same as above. Went to co-op at Dark10x's and forgot my keyboard.
Oni - did not work
The Bouncer - No analog nuthin' Not a good idea
Mutant Storm - Robotron dual stick style, it can work
Absolutely everything in MAME, save for lightgun or wheel games.