One of the things that I've done many times in my life is move logins from one SQL Server instance to another. There are various reasons for doing this, including migrating to new hardware, consolidating instances, applying permissions in a DR situation, and more. Over the years, I've depended heavily on sp_helprevlogin, which is kind of a bit of magic knowledge for SQL Server. There's no good reason that this procedure should stand out to you, other than it works. There are other methods of moving logins, but they're all a bit cumbersome.

This article will show you a better way by using the Powershell cmdlet, Copy-SqlLogin. This isn't from Microsoft, but rather from the impressive dbatools open source project. This cmdlet not only moves the login correctly, but it will also move the various instance level settings for the login, like default database, server roles, and more.

dbatools.io

I  had heard of dbatools.io quite awhile ago, but hadn't spent much time looking at the project. However, I attended a presentation in mid-2016 that truly impressed me. The ease with which many simple, but cumbersome tasks are handled by this set of cmdlets is truly impressive. My hat is off and I bow to Chrissy LeMaire, Claudio Silva, Mike Fal, Aaron Nelson, Rob Sewell and more. The entire team has done a fantastic job.

I am hoping to get some of these talented individuals to write more posts on how to use these cmdlets, so please, inspire them to do so.

Copy-SqlLogin

The Copy-SqlLogin cmdlet is quite powerful. In addition to copying a login from one SQL Server instance to another, this command will also ensure that these items are moved.

• SID
• password
• default database
• server roles
• server securables
• database roles
• database securables
• login attributes like password policy, language, expiration and more.

None of these items if that hard, but they can be cumbersome to manage. The default scripting in SSMS will result in this wonderful message:

/* For security reasons the login is created disabled and with a random password. */

Managing your own script is hard, and Microsoft hasn't give us a good bulk way of managing logins. This cmdlet makes things much easier, and it's simple to install.

Setup

I want to set up a few logins of various kinds and see how this actually works. Let's build a few different logins in my system. I've got some of these in Windows, some are SQL Serer logins.

CREATE LOGIN [JoeDBA]
WITH PASSWORD = N'someStrong7$Password',
    DEFAULT_DATABASE = [sandbox],
    DEFAULT_LANGUAGE = [us_english],
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON;
GO
USE sandbox;
GO
CREATE USER JoeDBA FOR LOGIN JoeDBA;
GO
ALTER ROLE db_datawriter ADD MEMBER JoeDBA;
GO
USE master;
GO
CREATE LOGIN [SAHelper]
WITH PASSWORD = N'AS7p#rStr0ngP@ssword',
    DEFAULT_DATABASE = master,
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = ON,
    DEFAULT_LANGUAGE = [Norsk];
GO
ALTER SERVER ROLE sysadmin ADD MEMBER SAHelper;
GO
USE [master]
GO
CREATE LOGIN [PLATO\DJ] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [PLATO\DJ]
GO
USE [sandbox]
GO
CREATE USER [PLATO\DJ] FOR LOGIN [PLATO\DJ]
GO
USE [sandbox]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [PLATO\DJ]
GO
USE [sandbox]
GO
ALTER ROLE [db_owner] ADD MEMBER [PLATO\DJ]
GO 

This gives me these logins:

• JoeDBA - SQL login, default to sandbox with a db_datawriter role
• DJ - Windows login, default to master, with dbcreator role and db_ddladmin in sandbox
• SAHelper - SQL login, new default language, sysadmin role.

These exist on my .\SQL2016 instance, but not on the .\SQL2014 or .\SQL2016_Qa instances. Here is my SQL 2014 logins folder:

And here is the .\SQL2016_QA instance.

That's the starting point.

Let's Use the Cmdlet

How can I move logins? Well, I have this simple way. I just type

Copy-SqlLogin

at the PowerShell prompt. As you can see, there are required parameters, so I'll need to input a source. 

The default is to copy all non-system logins (those without a ## starting the name), and I don't want to do that. I'll cancel out and try this again. 

The documentation page at dbtools.io is sparse, but I can use Get-Help to learn more. When I do that, I get lots of information.

To copy a specific login, I can specify the source and destination and use the -Login parameter. I'll do that to copy one login. Let's start with JoeDBA. Here's the command

Copy-SqlLogin -source .\SQL2016 -Destination .\SQL2014 -Logins JoeDBA

Here is the output from my PoSh window.

Fairly straightforward. One line, a login copied with the details. If I look at my logins for the SQL 2014 instance, I see this:

The properties are here:

and the user mapping tab:

This is a complete copy of the login from one instance to another. What if I don't have a "sandbox" database? I don't on the .\SQL2016_QA instance. Let's transfer this login, and the [SAHelper] login as well.

Both appeared to move. The JoeDBA login looks the same as on the SQL 2014 instance, except there is no user mapping transferred. Since there isn't a sandbox database, this makes sense. Since this isn't a problem in that I'm moving logins, not users, I am glad no error is shown above.

What about SQLHelper? If I check the properties, note that only one password policy box is checked and the default language is correctly set.

If we check the server roles, these are also correct:

If I try to log in with the same password to both the .\SQL2016 and .\SQL2016_QA instances, I am able to do so. That's impressive.

What about Windows logins? Let's try. I'll just put in the login name.

This makes sense. The login isn't [DJ], as you can see. It's [Plato\DJ], which is my local security store. It is slightly disturbing to see the entire list of logins, but I am performing a sysadmin, privileged function, so perhaps that doesn't matter.

In the screen below, I actually only typed "-Logins pl" and hit TAB. Autocompletion found the first login and I could tab through them al.

This doesn't work, and is a limitation that needs to be listed. Only a domain account works, not a local machine account. I think this is a bug, but since most people will likely be moving logins in a domain situation, perhaps this doesn't matter. I don't have a good domain for testing this right now, but this bears noting.

Multiple Logins

I can use multiple logins in the parameter as well. I'll create SQL logins for DJ and KJ, and then transfer them as well.

Note that I get warnings here about the database not existing. Not sure why this didn't appear earlier. In any case, these were transferred correctly and I could log into the new instance with the accounts.

Wrapping Up

I've tried to give you an idea of what you can do with this cmdlet. There is plenty more and I'd love to see some others document and showcase how other parts of the functionality work. If you do so, let me know and I'll link your piece in here.

If you'd like to see this in action, Chrissy Lemaire has a video of the cmdlet in action.

I think this is a great way to quickly, and easily move logins between instances. I don't do this often, but I have had to do this with servers, and certainly at scale when we might be rolling out some software or a user across mutliple systems. Having this easily run from a PoSh line means I can also implement this in CI/CD situations and ensure logins move where needed.