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)

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: 6685 | Views in the last 30 days: 205
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...


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

Already a member? Jump in:

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