The way that I have it set up is as you said, to add stored procedures to schemas of which certain roles are granted the relevant permissions.

Users are then added to the roles as necessary.

I do have quite a simplistic set up so there may well be a much cleverer way to achieve what you need.