I went to Grand Cayman in September, it was hot and sunny the whole time with an occassional sprinkle. Tropical locations have a sprinkle or two on a regular basis (or the lush greenery wouldn't be there). Little Cayman and Cayman Brac close down in September because it is the off season (kids are back in school and the snow birds have not started flying south for the winter) and because it is the start of the hurricane season, so they use this period to make repairs and give the staff some time off - so it's really the luck of the draw on the weather. If there is a tropical storm or hurricane, you will not be happy but odds are this will not happen - so go, but get trip insurance if you are worried.