When working with database that serve a large amount of users, the task of granting and managing your users database permission could get tedious and, sometimes, confusing. Things would get a little more complicated when you have to grant each user or group its own schema with a set of database permissions that would allow the user or group to create objects in one database and query the data from the other database. Because of this, the below stored procedure code should help you out with setting up an environment for your users and save yourself the extra steps of manually creating group, schema, and granting permission.

For the purpose of this post, I have a database name SourceDB which holds the core data that the users will use. Since this is a SourceDB, no users will have permission to create, modify, or delete. The only permission they would have here is SELECT. I also have a database name UsersDB where it would allow the users to create objects such as tables, views, stored procedures, etc. for their own usage such as reporting.

Here are the prerequisites that need to be done before using the stored procedure code:

Once those are done, the below stored procedure code will help with the rest of the tasks. This code can be used whenever you would like to set up a new environment for a user or group.

The below code runs on the UsersDB database. It creates login, schema, role, grants permissions to the role in UsersDB database, and, finally, adds the user’s role to the UserPerm role in the SourceDB

This stored procedure requires two variables: one for the schema name, and one for the username or group.

** Due to its admin functionality, this code should be under a schema which none of your users would have permission to. In my case, it’s dbo. At the same time, make sure you test the code on your development environment prior to apply or use it on your production server.

Because of its generic nature, you can recreate this stored procedures on any other databases that require the same setup. Of course, you will need to make sure the database name in the code is correct.

This stored procedure will definitely save you a good amount of time, especially, when you have to add multiple users or working environments in the user database.