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