One of my blog readers was referring below blog SQL SERVER – Create Login with SID – Way to Synchronize Logins on Secondary Server. Let us learn in this blog post about the script to sync logins between replicas.
He sent me email asking if there is a script available to create multiple logins with SID and password? I was able to spend some time and referred various online blogs/content to come up with below script.
Please let me know if you find below script useful.
SELECT 'create login [' + sp.name + '] ' + CASE WHEN sp.type IN ( 'U' ,'G' ) THEN 'from windows ' ELSE '' END + 'with ' + CASE WHEN sp.type = 'S' THEN 'password = ' + master.sys.fn_varbintohexstr(sl.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(sl.sid) + ', check_expiration = ' + CASE WHEN sl.is_expiration_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END + 'check_policy = ' + CASE WHEN sl.is_policy_checked > 0 THEN 'ON, ' ELSE 'OFF, ' END + CASE WHEN sl.credential_id > 0 THEN 'credential = ' + c.name + ', ' ELSE '' END ELSE '' END + 'default_database = ' + sp.default_database_name + CASE WHEN len(sp.default_language_name) > 0 THEN ', default_language = ' + sp.default_language_name ELSE '' END FROM sys.server_principals sp LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id LEFT JOIN sys.credentials c ON sl.credential_id = c.credential_id WHERE sp.type IN ( 'S' ,'U' ,'G' ) AND sp.name <> 'sa' AND sp.name NOT LIKE 'NT Authority%' AND sp.name NOT LIKE 'NT Service%'
How to use the script?
You need to run above script on primary replica. Once we execute this we would get a result which would have commanded to create a login. Copy the output and paste in a query window. The query needs to run against secondary server.
If you are using the Windows login than create them in every instance. In windows login case, the SID is managed by active directory, so you will be able to access in all replicas members of an availability group if the login exists in the primary replica.
Please note that user information is part of the database and it would come automatically via data synchronization.
Reference: Pinal Dave (https://blog.sqlauthority.com)