I think that you do have to be a WDW resort guest (probably an insurance thing) but the only childcare club that is supposed to be only open to guests of that resort is at the GF. (We were allowed to use the GF one when we were staying at the AS's as we were dining at V&A's, and it was a very off night--there were only three children in the club!)
At any rate, we also used the Sandcastle club a couple of times in the past. DS liked it well enough--it wasn't his favorite of the kids clubs, but he was fine there for a few hours one night. He was older then though, about 11 or 12, and most of their activities and games were geared for younger kids.
As long as your son doesn't have any seperation anxiety he'll probably love it there!
Keep in mind that they don't open until about 4:30 or 5:00, so make sure MGM will be open later on the night you want to drop him off. I'd also make reservations in advance, as not that the Harbour Club is closed and the BCV are open, that club is handling the children from what amounts to four resorts, and is usually pretty full.
Have fun!
Anne