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!

Steve

Total article views: 7724 | Views in the last 30 days: 74
 
Related Articles
ARTICLE

Automatically Scripting Out Permissions for Cross Environment Restores

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

FORUM

DBO permissions on database replaced during RESTORE

DBO permissions on database replaced during RESTORE

FORUM

Restore new databases - necessary permissions

Restore new databases - necessary permissions

SCRIPT

Script DB Level Permissions v4

Script database and object level permissions for all database users

BLOG

Restore database permissions

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

Tags
logins    
permissions    
roles    
security    
 
Contribute