Scripting permissions using catalog views in SQL Server 2005Last
updated: November 11th '06 |Best viewed with: All popular
browsers | Best viewed at:1024x768 |Links to external
sites will open in a new window

Want to keep in touch with the
latest in SQL Server world? Email vyaskn@hotmail.com with
'subscribe' in the subject line

Scripting permissions using catalog views in SQL Server 2005

Have you ever been asked to copy or clone the database permissions of one user to another database user? Or how about creating a new database role based on an existing database role? Basically, there isn't a direct command or interface for duplicating the permissions of a user or database role. One way of doing this would be to generate scripts for all objects within the database using SQL Server Enterprise Manager (SQL EM) or SQL Server Management Studio (SSMS), and then manually extract all the GRANT and DENY commands from the script. But it is a manual and tedious process.

Recently, I faced a situation where I had to create a new database role, that would get all the permissions of an existing role, plus some additional permissions. I really didn't want to nest the roles (adding a role as a member of another role), as I prefer to keep things simple. So, I came up with a SQL script, that generates the required commands to duplicate the permissions of a specified database user or role. This script makes use of the newly added SQL Server 2005 security catalog views to recreate the permissions.

sys.database_principals: Contains information about all database users and database roles

sys.columns: Contains data about each column of an object that has columns, such as views or tables

Note:In SQL Server 2000, the above catalog views are not available and the equivalent system tables are: syspermissions, sysprotects, sysobjects, sysusers, syscolumns

To use the below script, you will have to change the values of the @OldUser and @NewUser to the names of 'the user or role from which to copy the permissions from', and 'the user or role to which to copy the permissions to' respectively.

Note: This script will not automatically run the commands to copy the permissions. It will simply generate the commands that are required to copy the permissions from one user or role to another user or role. You will have to copy these commands, verify the generated commands, and run those commands manually in either Query Analyzer or Management Studio. It is better to run the below script with the output set to text mode, instead of grid mode. Also note that, this script cannot be used to script permissions for fixed database roles like db_datareader and db_datawriter. It is meant to script permissions for database users and user defined database roles.

If you just want to script the permissions of an existing user (and not copy them to a different user), then simply set the values of the variables @OldUser and @NewUser to the same user or role name.

The output of the script contains three sections:

- sp_addrolemember calls to copy the database role memberships of the specified user or role

- GRANT and DENY commands to copy the object level permissions of the specified user or role

- GRANT and DENY commands to copy the database level permissions of the specified user or role