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

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.