Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have to copy the entire database permissions from database A to database B which has same objects in database B in same instance.
What are the things that I need to copy from database A to database B to get the same permission as in database A.
Environment: SQL Server 2012 , in same server with same logins.
I use powershell based Sync-DbaSqlLoginPermission
This command syncs SQL login permissions from one server to another including login permissions, roles, database permissions and more. By default, all logins are synced. The -Logins and -Excludes parameters is autopopulated for command-line completion and can be used to sync only specific logins.
If a matching login or securable does not exist on the destination, the permission sync will be skipped.
Credential removal and application role syncs not currently supported.
Or You can use TSQL based Script DB Level Permissions v3
You could use SSMS for this. Right-click on the database -> tasks -> generate scripts.
Generate Scripts can script out permissions, but the method is pretty hidden.
When you get to the screen where you choose the location to script things out to, click the Advanced button. Scroll through there until you see the "Script Object-level Permissions" option, and set it to "True". The script will then include permissions for whatever you scripted out.
For anyone else who finds this thread, the latest version of this useful script is here:
