Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Reverse-Engineer Database Permissions (2005 up)

By george sibbald,

Run this stored proc from the database. It will create the SQL statements required to re-assign permissions to users defined in the database. This script will work for SQL2005 and above. There are a number of scripts out there to do this for SQL2000, and whilst these still work for SQL2005 and above they can create problems when run back in as old style syntax is used and schemas are not accounted for.

The stored proc is most useful for storing away current permissions in a database before it is overwritten by a restore from a different database, especially prod to test refreshes. After the restore you can then use the script output by the proc to recreate the permissons for the dev users. I would scan the output first and run it in sections to recreate just the permissions you need rather than just execute the whole script. With the aid of exec sp_helpuser you will need to decide for yourself what to do about users carried across from the other environment!

The stored proc can also be used to document permissions for auditing or perhaps DR purposes.

With so many types of principals and securables now there may be some edge cases not covered but this should cover 99% of cases and certainly fulfills my needs. For application roles I have used a placeholder of 'insertpwdhere' for the password as it must be specified in freetext when creating an application role and it would be a security risk to display that, and anyway there does not seem to be a way to reverse engineer the password.

Total article views: 2727 | Views in the last 30 days: 33
 
Related Articles
FORUM

scripting a database without db owner permissions

scripting a database without db owner permissions

SCRIPT

Permission scripting over all databases

Scripts permissions/roles for db user across all databases.

SCRIPT

Stored procedure to script out user permissions

Stored procedure to script out user permissions

FORUM

permissions

permissions

FORUM

permissions

permissions

Tags
permissions    
principals    
securables    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones