Clone a SQL Server login and password to a new server

By:   |   Comments (10)   |   Related: > Security


I have a SQL Server 2014 instance ServerA and I need to setup a SQL Server login on ServerB with the same credentials as ServerA for testing.  I don't have the password, but I need to keep the password the same on both servers.  How can I clone a SQL Server login onto another server and keep the same password?


In my scenario, the login testlogin on ServerA already existed, but to show each step I will create a new login for better clarification.

Create New Login Using SSMS on ServerA

In the SSMS Object Explorer expand Security and select Create New Login.  Here I am using SQL Server authentication and creating a new login testlogin.

Create login testlogin

Generate Login Script to Use on ServerB

On instance ServerA, right-click on the login testlogin and choose Script Login as > CREATE To > New Query Editor Window.

Generate login Script

This will generate a script like below.

USE [master] GO
/* For security reasons the login is created disabled and with a random password. */
CREATE LOGIN [testlogin] WITH PASSWORD=N'8ic8fZq1PEbEbG3qyaQpn1ih/IrJeBW0MzcKU0KDQVI=', 

Apply Login Script on ServerB

I applied this script on instance ServerB and did not include the DISABLE line as shown below.

USE [master] GO
/* For security reasons the login is created disabled and with a random password. */
CREATE LOGIN [testlogin] WITH PASSWORD=N'8ic8fZq1PEbEbG3qyaQpn1ih/IrJeBW0MzcKU0KDQVI=', 

The script ran successfully on ServerB, but the login for the application failed. In the comments of the code, you can see that a random password was generated, so this is why this did not work.

Process to Create a Working Login

I then deleted the login testlogin on ServerB and started with this approach.

Connect to instance ServerA using SSMS and open a new Query window and run the below query in the master database. Also, review the comments I put in for each section of code.

/*SUSER_SID() - Returns the SID of the current security context*/
USE [master]
SELECT SUSER_SID('testlogin')

/*This is the SID value in varbinary for login testlogin
0xE7F3C36B478F5A4A96F179210CFF39C5 */

/*LOGINPROPERTY('testlogin','PASSWORDHASH') - Returns the hash of the password*/
USE [master]

/*This is the password hash for login testlogin
0x020019814E12C5DCBE7D55C803E46D9CD7E349C9F9000BC392759E0CFD0CF98AA5A3D88B1A725F660A82FE7CAEAECA34E49AC5F08C188F5EF5DB99B06EC1E290EBFF4DF10EF1 */

Now use the testlogin SID and the password hash string value as shown below and then run the script on instance ServerB to create the login with the same password.

CREATE LOGIN [testlogin] WITH PASSWORD = 0x020019814E12C5DCBE7D55C803E46D9CD7E349C9F9000BC392759E0CFD0CF98AA5A3D88B1A725F660A82FE7CAEAECA34E49AC5F08C188F5EF5DB99B06EC1E290EBFF4DF10EF1 HASHED, 
SID = 0xE7F3C36B478F5A4A96F179210CFF39C5, 

On instance ServerB the login testlogin was created and a login by the application was made successfully.   I still don't know the password, but at least the application is working with the password that is hardcoded into it.

Next Steps
  • Check the login SID to see it exists before running on destination server.
  • I tested with SQL 2014 Developer edition, but this should work with other versions.  Just test the code in a test environment prior to production.
  • Please test this code in development or QA environment before rolling out to production.
  • Check out these other Security tips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, May 9, 2021 - 9:56:34 AM - RaymondZheng Back To Top (88660)
Wonderful tips. It helps me on my work today. Thank you very much!

Wednesday, November 18, 2020 - 12:31:44 PM - Darren Back To Top (87805)
You're awesome! Worked like a charm, thanks.

Tuesday, September 8, 2020 - 4:54:52 AM - Robert Back To Top (86432)
Thank you very much! It works like a charm!

Friday, March 3, 2017 - 5:54:51 PM - Geordy Back To Top (47101)

 This was very helpful, but it wasn't until I scrolled all the way to the right that I noticed that you have to include the word "HASHED" after the password.

I would suggest making a clear note about this important fact.


Thank you for your article.



Friday, March 3, 2017 - 12:15:08 PM - Brian Hubartt Back To Top (47073)

 I use this Microsoft option that works really well...


Friday, March 3, 2017 - 4:00:45 AM - Perry whittle Back To Top (47040)

The whole point is that passwords should not sync across environments.

and if your application is hard coding passwords, it's probably time for a more secure, robust application.

Thursday, January 26, 2017 - 12:10:56 PM - Bhavesh Patel Back To Top (45593)

Thanks @rob and @zhunya We have a way for powershell module for such kind of jobs.
But actually I didn't go for this apporach because source and destination server is different one and required to perform manually steps and not have powershell module.
But sure on automation kind of job We should use powershell way.

Thursday, January 26, 2017 - 10:11:57 AM - Rob Back To Top (45589)

You can also use the dbatools PowerShell Module to do this in one easy step

You can find about this command here

Thursday, January 26, 2017 - 4:43:37 AM - K.B Patel Back To Top (45579)



Nice tips Really I faced this issue in past.

Thursday, January 26, 2017 - 4:35:38 AM - Zhunya Back To Top (45578)

Better use Powershell module DBATOOLS ( ) set of Powershell functions for migrating, testing for best practice configuration, and just doind possibly any DBA work just with one cmd'let.

To clone login - Copy-SqlLogin -Source sqlserver -Destination sqlcluster -Logins netnerds

Greate time-saver module

get free sql tips
agree to terms