Skip Navigation LinksHome > Categories > Code from a Category

Scripting permissions using catalog views in SQL Server 2005



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1845
Add Date: 12/28/2011
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.

This script queries the following SQL Server 2005 security catalog views:

sys.database_role_members: This catalog view maps database users to database roles that they are members of
sys.database_permissions: Contains information about all the permissions held by users and roles
sys.objects: Contains information about all user-defined database objects
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

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.