In one of my previous posts i shared a script to generate all permissions for a database. Couple of days back a blog reader commented on post and asked for a script that may generate permissions for a specific login in all databases. Such script would be helpful for servers with many databases. I have tried to accomplish the task by combining the previously posted script with some other procedures.
Whole script is composed of 6 steps/modules. Let us have a look at 6 steps that together build whole script.
- Create a procedure (USP_GeneratePerm) to generate permissions
- Create a procedure (USP_CreateInAllDBs) to create USP_GeneratePerm in all databases
- Execute USP_CreateInAllDBs with sp_MSForEachDB
- Create a procedure (USP_ExecuteInAll) to execute USP_GeneratePerm in all databases
- Execute USP_ExecuteInAll
As we can see in above steps that three stored procedures are created and executed. Last step would be to drop the created procedures.
- Prepare your mind that this script may need some modifications according to your scenario. Especially the statements where system/READ ONLY databases are need to excluded from the databases in which our sp would be executed.
- Script would not be generated for READ ONLY databases. So for READ ONLY databases permissions may be generated separately.
- Rather than to execute whole script at a time. Execute each of six steps isolated.
Create a procedure (USP_GeneratePerm) to generate permissions
A stored procedure would be created in master database. Procedure USP_GeneratePerm would be accept two parameters for database name and login. It would actually generate the permission statements.
Create a procedure (USP_CreateInAllDBs) to create USP_GeneratePerm in all databases
The procedure USP_generatePerm is required to be created in all databases other than system and READ ONLY databases. So to complete this requirement we have to create another procedure USP_Create InAllDBs. It would also except two parameters. One is name of database where primary procedure would be created and second is name of primary procedure. In our case primary procedure is USP_GeneratePerm.
Execute USP_CreateInAllDBs with sp_MSForEachDB
In third step we would execute USP_CreateInAllDBs in all database by using sp_MSForEachDB system stored procedure. I have exclude the system databases in script.
Create a procedure (USP_ExecuteInAll) to execute USP_GeneratePerm in all databases
No we have our primary SP created in all databases other than ssytem and READ ONLY databases. Now it is required to execute the primary SP in all databases. For this purpose we would create another SP named USP_ExecuteInAll. It would accept one parameter that is login for which script is required to be generated.
Execute the above created USP_ExecuteInAll by providing login as parameter.
Drop the procedures created in mater and other databases.
According to above descriptions, it is obvious that login for which permissions are required to be generated is provided in step # 5 i.e. Execute USP_ExecuteInAll.
Click here to download the script file. If any one could help to further improve the script and reduce its complexity then you are welcome with due credit on this blog.