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

SQL
12 Comments

SQL SERVER - AlwaysOn Availability Groups: Script to Sync Logins Between Replicas? script 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

AlwaysOn, SQL Error Messages, , SQL Server, SQL Server Security
Previous Post
SQL SERVER – RDP Error to Azure Virtual Machines – The remote computer that you are trying to connect to requires Network Level Authentication (NLA)
Next Post
SQL SERVER – Execution Failed. See the Maintenance Plan and SQL Server Agent Job History Logs for Details

Related Posts

12 Comments. Leave new

  • thanks as always for sharing your expertise and for the script Pinal. My question is how is this script different from sp_help_revlogin we use in our environment

    Reply
  • Hi Pinal,

    Thanks for nice article,,

    Could you or anybody please help me on this:

    We are looking to upgrade our Prod servers from 2012 to 2016.
    I installed Microsoft Data Migration Assistant on may machine, I was testing with our test and Dev servers for practice, this is first time I am doing this:)

    But I am getting error on fist step itself.

    Error:- There are validation errors in the source or target server. Please fix the issues and go to the next step

    I have SA privileges, we use windows authentication, no authentication problem on any of the servers

    Thanks a lot

    B Raj

    Reply
  • Thanks Pinal. Great post! Any chance you can compare this to using contained logins in a Pros/Cons post?

    Reply
  • HI Pinal,

    validate database users within an AG across its nodes in sql server

    I need to proactively validate database users within an AG across its nodes, by checking the users SSID and password, please help me with script.

    There was an incident caused by missing SQL Account logins on a node of an Availability Group.

    It might be due to the following reason User is added to the AG node but has different SID than the primary node, so user has no access to the database, • User is added to AG node with a different password than the other server has.

    Reply
  • Thanks for script, its working fine.

    Reply
  • This doesn’t seem to port over the Server Role access

    Reply
  • run exec sp_change_users_login ‘report’ to see which users are “broken”, they use the same procedure to fix
    exec sp_change_users_login ‘DB_User’, ‘auto_fix’

    Reply
  • It was very helpful and working like charm

    Reply
  • can some have script to fetch sid mismatch login details in HA servers

    Reply
  • It’s great script and very useful for DBA’s

    Reply

Leave a Reply