Click here to monitor SSC
SQLServerCentral is supported by Redgate
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)

Script DB Level Permissions v3

By S. Kusen,

This T-SQL code will script out database and object level permissions to help make it easier to backup and restore databases, and subsequently restore the permissions.  A primary use case would be to restore a production database to a lower environment where users have more permissions in the lower environment.  

You could then:

1. Script out the permissions using this script in the lower environment.
2. Restore the database.
3. Restore the permissions from the output from step 1.

It seems to work on SQL 2005-2014, so have at it and let me know in the comments if you come across any problems, and I'll be sure to update the script.

Hope you enjoy!


Total article views: 7036 | Views in the last 30 days: 163
Related Articles

Automatically Scripting Out Permissions for Cross Environment Restores

This article from Warren Campbell shows a process to recreate permissions in development environment...


DBO permissions on database replaced during RESTORE

DBO permissions on database replaced during RESTORE


Restore new databases - necessary permissions

Restore new databases - necessary permissions


Restore database permissions

Introduction: I came across a couple of subtle gotchas around the permissions required to restore a...


Database Restore – Permission

I have DBO access and I do have the baseline database in my workstation where I wanted to restore to...