Copying SQL Logins Between Instances

Migrating SQL databases is fun, depending on your definition of fun that is. The process can involve having to move things such as login details that have been around for that long that nobody has a clue what they are anymore.

With domain accounts that's pretty straightforward, the passwords are managed in Active Directory and not held in SQL Server and it's just a case of recreating the account on the new instance.

SQL authentication is different and migrating an account as is means you also have to recreate the password as is which could be difficult if you didn't know what the password should be. It's worth saying at this point that the preference should always be to use domain accounts, they're more secure and much more manageable and migrations are ideal opportunities to refactor things to be better but for the sake of this article let's proceed with the scenario of recreating a SQL authentication login with an unknown password, and we'll need to get creative.

Now when I say creative what I mean to say is look at the official documentation! Here's a link from Microsoft that tells us how to move logins from one instance to another. The link contains code to create two stored procedures sp_hexadecimal which is a password hashing algorithm and sp_help_revlogin which uses sp_hexadecimal and generate scripts to recreate our logins and passwords. I've ran the code on my test instance and now to test I'll create the following SQL login:

CREATE LOGIN TestLogin WITH PASSWORD = 'ThatsAReallyGreatPa$$Word!'

To generate the create login scripts all I need to do is execute sp_help_revlogin to run a script containing create commands for every login (domain and SQL) on my instance and right at the bottom of the script I can find the login I've just created:

-- Login: TestLogin

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'TestLogin')

BEGIN

CREATE LOGIN [TestLogin] WITH PASSWORD = 0x020014E5221D2BDC53D7B9F629237230B4B3AFCA88DF1E6733391626C0B267D5D2544CEA7B9BC720087076B8DF2A564622960F870C90085B84C0D2FB08875F225B67874E24C7 HASHED, SID = 0x34CA86032AA9634388A89BCD72A65E21, DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

END

Now all I have to do is run the code above on a different instance of SQL Server to recreate the login with exactly the same password as the existing one.

That's pretty neat, but it would be far neater if I didn't have to go around creating new system stored procedures, trawl through what could be a huge amount of logins to find the right one(s) and do the whole thing in one line of code, enter the dbatools PowerShell module and Copy-DbaLogin.

In PowerShell I can simply run code, just like the following to copy a SQL login to another instance of SQL Server and I can login using the existing password without it being exposed to me, so it's still an unknown but it's recoverable:

Copy-DbaLogin -Login TestLogin -Source localhost -Destination localhost\MSSQLSERVER01 

Now with great Power(Shell) comes great flexibility (yes I came up with that all by myself) and this is one of the real strengths of using dbatools. Instead of one login I can specify multiple ones:

Copy-DbaLogin -Login TestLogin, AnotherTestLogin -Source localhost -Destination localhost\MSSQLSERVER01 

Or if I just want to copy every single login (Windows and SQL) from one instance to another I can just leave the login parameter entirely:

Copy-DbaLogin -Source localhost -Destination localhost\MSSQLSERVER01 

If any logins exist on the destination instance already they get skipped over but I can also use -force which forces the drop and recreation of any logins on the destination:

Copy-DbaLogin -Login TestLogin -Source localhost -Destination localhost\MSSQLSERVER01 -force

Finally, if I don't want to copy any logins but create a file with the login create scripts I can do that as well:

Copy-DbaLogin -Login TestLogin -Source localhost -OutFile c:\migrate\logins.txt

Which outputs a text file containing the following:

USE master

GO

IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'TestLogin') CREATE LOGIN [TestLogin] WITH PASSWORD = 0x020014E5221D2BDC53D7B9F629237230B4B3AFCA88DF1E6733391626C0B267D5D2544CEA7B9BC720087076B8DF2A564622960F870C90085B84C0D2FB08875F225B67874E24C7 HASHED, SID = 0x34CA86032AA9634388A89BCD72A65E21, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF, DEFAULT_LANGUAGE = [us_english]

GO

So there's two ways to move logins from one instance of SQL to another that will also recreate the logins with the current passwords.