All three can be great, with good diving all around...
The general caveat that I give is that Grand can be crowded at times, and if you're the type who's after really fine dining, its going to be Grand ... with $100/pp dinners at places like the Wharf ... or to visit Gladys at Pirate's Point on Little. This isn't to say that the buffets at places like LCBR or BRBR aren't good food - - its just that the level of fine dining pretty much can't ever come from a row of steam trays.
Little Cayman is a bit over rated as far as the diving goes. There is so much diving pressure throughout the year on the limited number of sites on the north side that the soft stuff is really getting beat down. Having said that, it is still one of the best places to dive in the Caribbean besides Bonaire, Roatan, and a couple of others. We do LCBR every August and have noticed a deterioration every year... Brac is a notch down in dive quality but is now getting much less dive pressure.
I've also seen the decline in Bloody Bay on LC too, and its been mentioned here at times. While it does cause some unhappiness, most first (or few) -time visitors are able to appreciate it for how it currently is, without the knowledge (and thus regrets) of the "how it was". What can help here is to be proactive with your dive operator to ask them to take you to places that are "Good, but not necessarily famous". This might get you to Splash House, Fisheye Fantasy, Grundy's Gardens, etc, which get less diver pressure. Insofar as the Brac, hurricane Paloma gave it a major rest from any diver pressure and in general, the diving is IMO equal to LC and in some ways better: what I typically see as the differentiator for "better" is frequently that the famous dives on LC start with shallower drop-offs, which allows for longer dives which are often preferred. To summarize Brac diving, it is "big topology", where you're going to be swimming within the reef/wall and not merely swimming over it. The price you pay for it is that the dives will trend to be deeper.
To go/not go during hurricane season (the offers sure are tempting...)
Statistically, September is the highest risk ... anywhere within the Caribbean, not just Cayman per se. Nevertheless, it is one of our favorite times of year to go, since the heat of the summer has subsided, but the water is still warm. One can get the SE wind blowing pretty well at times, but one side or the other of the island is invariably in the lee for diving. Save money by going down for two weeks instead of one
-hh