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)
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
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
Thanks Pinal. Great post! Any chance you can compare this to using contained logins in a Pros/Cons post?
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.
Sorry, don’t have it handy.
Thanks for script, its working fine.
Your welcome Nitin.
This doesn’t seem to port over the Server Role access
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’
It was very helpful and working like charm