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. 

Restoring SQL Server Databases With Standby

Here's a scenario. A user has made several modifications to a database and now needs to restore the database back to a particular point. The problem is that they don't know the particular time to restore back to, just that they need the database back to before a particular change was made.

If the database is in simple recovery then there's no options to play with, the database can only go back to the last available full backup (and maybe differential backup if they've been taken). If the database is using full recovery (I'm skipping over BULK-LOGGED for this post) then we can then apply the transaction log backups taken after the full backup to get back to a point in time by restoring the database with NORECOVERY and then restoring the necessary log backup files until we reach a particular point either by using .

But one of the disadvantages of NORECOVERY is that it doesn't give us a readable database until we restore with RECOVERY and at that point we can't restore further log backups to our database so if we have missed anything we'd need to start the whole restore process from the beginning.

Thankfully SQL Server gives us an option to restore databases and log files and enable us to read the database between restores using the STANDBY option.

Standby maintains the ability to restore additional transaction logs or differential backups similar to NORECOVERY but also puts the database into read-only mode enabling us to view our database content. This means after each restore we can look into the database, check for our changes and if necessary apply another transaction log restore and when we're happy we can then restore the database with recovery.

Here's a very simple example where I'll restore a full backup of my imaginatively titled Test Database to an even more imaginatively named database called TestCopy. The magic as it were is all in the STANDBY clause highlighted below.

RESTORE DATABASE TestCopy FROM DISK = N'C:\Backup\Test_Full_1312.bak' 

WITH MOVE 'Test' TO 'C:\Data\TestCopy.mdf',

MOVE 'Test_Log' TO 'C:\Logs\TestCopy.ldf',

STANDBY = 'C:\Backup\Test_Standby_1312.bak'

Here's what BOL has to say about the STANDBY file we've just specified in our command:

"The standby file is used to keep a "copy-on-write" pre-image for pages modified during the undo pass of a RESTORE WITH STANDBY. The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores."

Now the database has restored an in SSMS and the database the name has (Standby / Read-Only) next to it which means I can jump right in and query data (updates aren't allowed though) but in addition if I want to restore the next log file I can without having to go back and start again with the full backup.

RESTORE LOG TestCopy FROM DISK = N'C:\Backup\Test_Log_1312_0700.trn' WITH 

STANDBY = 'C:\Backup\Test_Standby_1312.bak'

After the log restore I can check the database content again and if I'm happy I can restore the database with recovery (RESTORE DATABASE TestCopy WITH RECOVERY) to bring it fully online or if further log backups are needed to be restored I can keep using STANDBY like in the example above until my I'm happy the database can be recovered fully. Worth mentioning that the standby file is removed when the database is fully recovered.

Standby is a great feature of SQL Server database restores and if you're thinking that's great but how do I do it in PowerShell because that's what all the cools kids are using these days then you're in luck as Stuart Moore has a great guide to restoring databases including with standby using DBA Tools here!