Sync SQL Logins

Method 1

Microsoft provides a couple of stored procedures you can use.  It’s not automated, but you could automate it yourself.  Once you create the SPs and run sp_help_revlogin it gives you the scripts to run on the partner to create the logins.  It’s not the prettiest method since it was designed for a 1 time transfer, but it works.

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

Method 2

You can use SSIS to sync SQL logins.  This method has some gotchas, but I’ll leave that to the reader.  The good news is that it is automated.

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

 

Method 3

Contained databases.  A contained database keeps the entire user authentication mechanism within the database without relying on logins that sit outside the database in Master.  You can read more about contained databases here.

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

Method 4

There is an awesome script to automate this process.  This method is my personal choice.  It works great and is easy to automate.  You can run it with SQLAgent job on sql.

Note : This script creates a stored procedure to handle the move and also uses Linked Servers.  

For script please go through below link and hope this helps you a lot.

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