A potable water tank could work, but I suspect you'll find there are many restrictions so do your home work. You'll want to size it right also. Fresh water tanks run from 15gal to over 100gal and folks vary on how often they need to refill.
Even if you have folks just come out for the weekends and leave the RV there, they will need to be able to charge the batteries. Many folks want electric so they can run the fridge while they are away.
As I'm thinking, this is how I'd go about it:
I would look into insurance for letting them keep the RV on your property for the season Get that priced along with liability insurance for injuries, fire insurance, etc. That gives you a minimum cost for renting.
With that, I would try word of mouth and see if you can find a few folks interested in primitive "pack it in, pack it out" camping for a season at just above the insurance costs. No amenities. You can provide a map showing the closest dump station and, if available, place to refill fresh. They provide their own recharging system. Put in fire rings - otherwise they will burn down your property.
This will give you real life data on whether this is viable. If the insurance is more than people will pay, then it's not worth all that extra work and cost to upgrade - you won't be able to charge enough over the base price to cover insurance AND upgrades.
If the first season goes well, you could add picnic tables and charcoal grills for the next season. Otherwise keep it primitive.
If after a few years, you find that people would like potable water onsite, you can then look into adding a tank.
This gets you started with the least amount of investment and gives you time to work out the practicalities.
