I was in Borden till last summer, as a service couple. We paid $10 per month for the kids to have a card (family membership), but they didn't give us cards, since we had military ID. I asked the question outright if we (hubby and I) needed to pay, since we were debating whether we needed the family membership since the kids were young. We were told it was just for the kids, since we were military. Used the gym, the pool, the rink, and so on and didn't have to pay another dime (for us or the kids). Signed the kids up for various lessons and activities and got the member discount.
The facility there (not sure about other bases) is a combination of public/NPF funded. It is the CF National Sport centre, so it gets a lot of public money.