1

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.

3 Answers 3

4

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

0
0

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.

0

For anyone else who finds this thread, the latest version of this useful script is here:

Script DB Level Permissions v4.3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.