SQL SERVER – AlwaysOn Availability Groups: Script to Sync Logins Between Replicas?

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)