SQL Server how to synch Users and security permission with User Database Role ?

Question

Hello, I have 2 user DBs and Roles with same names on each on separate SQL Servers i.e.

• Server_Dev.[Orders] DB [Sales] Role
• Server_Prod.[Orders] DB [Sales] Role

What I'm stuck at is that the [Sales] Role Users & security permissions within each are different & not in synch.

Basically I'd like to transfer all of the missing Users & their security permissions from Source Server_Prod.[Orders] DB [Sales] Role that's missing in corresponding Target Server_Dev.[Orders] DB [Sales] Role

Is there a way to do this?

(I don't have enough disk space to simply restore over entire Target Server_Dev.[Orders] DB from Source Server_Prod.[Orders] DB being the easiest method.)

Thanks in advance.

Answer 1

Take a look at Sebastian Meiner's blog post: https://sqlity.net/en/2584/script-database-permissions/

Answer 2

Hi techsearch,

Welcome to Q&A Forum; this is a great place to get support, answers, and tips.

Thank you for posting your query; I'll be more than glad to help you out.

Maybe you find your own solution in a combination of commands from the PowerShell module dbatools

https://dbatools.io/commands/#Login

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!

Answer 3

Hi @techresearch7777777 ,

Please refer to this MS document:

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/security/transfer-logins-passwords-between-instances

As for transferring all of the missing Users & their security permissions, please refer to this similar thread:

https://stackoverflow.com/questions/13766150/copying-user-roles-and-permission-from-one-database-to-another

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".