Sync Logins Between Alwayson Replicas and DB Mirroing


Sync SQL Logins

Method 1

You can use a couple of stored procedures provided by Microsoft. It’s not automatic, but it’s something you could do yourself. When you create the SPs and execute sp help revlogin script, you’ll get the scripts you need to generate the logins on the partner. Because it was developed for a one-time transfer, it isn’t the prettiest way, but it works..

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

Method 2

SSIS can be used to synchronize SQL logins. There are a few issues with this strategy, but I’ll leave that to the reader to figure out. The good news is that it’s all done automatically.

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/transfer-logins-task

Method 3

Databases that are contained. The complete user authentication method is kept within the database in a contained database, rather than depending on logins that are stored outside the database in Master. More information on enclosed databases can be found here..

https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-database

Method 4

This can be automated using an excellent script. This is the way that I like. It performs admirably and is simple to automate using SQL Agent job and can be used to schedule it.

Note : that this script uses Linked Servers and builds a stored procedure to manage the movement of Logins.

Please see the link below for the script, which I hope will be of great assistance to you.

https://sqlserveradmintutorial.blogspot.com/2018/01/sync-loginsusers-between-principal.html


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: