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)
Thank this author by sharing:
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!
This article from Warren Campbell shows a process to recreate permissions in development environment...
DBO permissions on database replaced during RESTORE
Restore new databases - necessary permissions
Script database and object level permissions for all database users
I came across a couple of subtle gotchas around the permissions required to restore a...