Copying and Moving SQL Server Logins

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:15
  -
  -
  -
  -
07:30
  -
  -
  -
  -
07:45
  -
  -
  -
  -
08:00
  -
  -
  -
  -
08:15
  -
  -
  -
  -
08:30
  -
  -
  -
  -
08:45
  -
  -
  -
  -
09:00
  -
  -
  -
  -
09:15
  -
  -
  -
  -
09:30
  -
  -
  -
  -
09:45
  -
  -
  -
  -
09:56

Hello and welcome to another SQL Server Video, my name is Michael Campbell and in this video we’re going to look at copying and moving SQL Server Logins from one server to another – along with making sure that those logins are correctly mapped to users and roles as needed.

And, if you’d like some background on why this is important – or if you’re looking for info on how to copy or move databases, then make sure to check out some of the other videos in this series.

Otherwise, let’s spend just a second or two covering why copying and moving logins is so important.

And to do that, we’ll start with a quick overview of some basics around the notion of logins – which are what SQL Server uses to control who or what can access a given SQL Server or ‘log in’ to it.

And what’s important to note about logins is that they represent a way to authenticate either an application or a user – typically in the form of making them provide a username and password.

With SQL Server, however, those authentication credentials can come from one of two places: either from Windows – which SQL Server trusts implicitly, or from SQL Server itself – meaning that SQL Server is responsible for managing and authenticating a username and password instead of merely relying upon Windows to validate the authentication of users and applications.

In turn, once logins are authenticated, they’re assigned permissions, or authorization, that define what these logins can or can’t do on the server. Then, in addition to defining system-wide permissions (such as the ability to create new databases or administer backups), logins can also be mapped to users or roles in individual databases – which define what a given login can or can’t do within a given database.

What’s important to note though, is that login information is stored on the server – while user and role information is stored in each database. As such, if you move or copy a database from one server to another, you’re typically going to lose the mapping between logins and users – resulting in what are known as orphaned users.
Therefore, in this video we’ll address the specific steps needed to address this problem using the following approach:

First, we’ll determine which logins we need to copy or move.

Second, we’ll look at scripting these logins with some code provided by Microsoft that will let us import these logins to a target destination server.

Third, we’ll quickly look at what you need to do to deal with Windows Logins – as their passwords or authentication details can’t be scripted by SQL Server.

Then we’ll conclude by looking at the process of recreating needed logins on the destination server and making sure that they’re correctly mapped to the users and roles in our transplanted databases.

So, with that order of operations in mind, let’s start by connecting to a server that I’ve already used as the source for copying a database in a previous video.

And, as you can see, I’m using SQL Server 2008 in this video, but the process that we’ll be following in this video will be pretty similar if you’re following along on SQL Server 2000 or SQL Server 2005 – and I’ll be sure to call out any key differences as needed.

Once logged in, I’ll open a new query against the database that I’ve already copied, and at this point we have two options for enumerating the logins we’ll need to account for from this database on the destination server.

First, we can run the special system stored procedure, sp_helplogins – which works on all versions of SQL Server – to get a list of all logins and their mapped users or roles on the entire server.

Or, if you’d like that information in a slightly easier to use format, you can download the code for this video from the code page for this video on the SSV site, and run a query that I’ve created in order to pull back sp_helplogins data for just the current database.

And, as you can see here, I’ve got three users that I’ll need to worry about with this database, two of which are SQL Server Logins, and the third is a Windows Login.

Now that we know which logins we need to address, we can take a look at KB article 918992 or 246133 to review an approach that will let us script logins from a source server for easy export to a destination server.

And, to spare you the horror of sorting through those KB articles, I’ll summarize the approach they outline, which is to give you some scripts you can use to create two new stored procedures on your source server in order to let you generate scripts for your logins which can be run on destination servers.

And one of the reasons that this KB article is so large – and spread, for all intents and purposes, across two different KB articles, is due to the fact that the syntax to create logins has changed a bit from one version of SQL Server to another. Therefore, determining exactly which of the scripts outlined in these KB articles to run depends upon two things:

First, the version of SQL Server on your source server, and second, the version of SQL Server on your destination server.

As such, you’ll want to pay careful attention to which script you grab from either of these KB.

To make things easier on you though, I’ve extracted these scripts into three different scripts that you can easily use by downloading the code that accompanies this video on the SSV web site.

Accordingly, since I’m going from a SQL Server 2008 server to another SQL Server 2008 server, I’ll just open up the files accompanying this video and use the corresponding script.

And to use it, first I’ll execute the part provided by Microsoft in order to create those two new special stored procedures, then I’ll go ahead and executed the sproc outlined in the KB article to generate the scripts for the logins on my source server.

And, as you can see, doing this generates a login creation script for EVERY login on my source server.

Given that you may not want to create all of these logins on your destination server, for obvious security reasons, you’ll typically want to go through the output of the sp_help_revlogin sproc to scrub out any logins that you don’t want migrated.

And, since I already enumerated the three logins that I need, I’ll just remove everything else, and you’ll likely want to take a similar approach in your own environment.

Another thing that you’ll want to do when manually reviewing these logins creation scripts is account for windows logins as well – as those logins will frequently be different from one server to another. So, for example, since my SQL15\WebApp user won’t exist on the server where I’ll be moving this login, I’ll want to manually change that to a principal and user that will exist. And, of course, the same thing goes for domain accounts – which you’ll also need to manually edit as well.

The bigger problem though, of course, is that since these are windows Logins, SQL Server isn’t able to script the ability to create these windows credentials on the target server – and, truthfully, we wouldn’t want that anyhow.

The point though, is that if I want my WebApp login to work on SQL16, I’ll need to go out and manually create that account BEFORE I run this script on SQL16.

And, by doing that, when I run this script, SQL Server will check with the security principal, SQL 16 in this case (or a domain controller if I were using a domain user), and make sure that the specified windows login exists.
If it does, then SQL Server will bind the specified windows user or group to this SQL Server login, and everything will work out as needed. Otherwise, this login creation will either throw an error, or – more typically – it will just fail silently.

Then, once these logins have been created on the destination server, running the special stored procedure, sp_change_users_login with the ‘report’ action, or option, will provide me with a list of any potential logins and their users or roles that may still be orphaned or non-mapped within my recently copied database.

Most of the time though, by creating the necessary logins on the destination server, SQL Server will go ahead and automatically re-map these logins to the necessary users and roles as needed – resulting in nothing being reported by the sp_change_users_login special stored procedure.

Of course, if you’re worried that you might be getting no results in error, you can always run the script we used in the first step in this video against your recently copied database as a way to verify that all logins, users, and roles have been correctly mapped within your destination database.

Otherwise, if logins do end up getting listed when you run this sproc, then you can run sp_change_users_login with different action parameters and additional details in order to fix any orphaned users that may still exist.

So, for example, by using the ‘Update_One’ action, you can link a specified user to an existing login – which is not only a great way to remap orphaned users, but it also gives you the option to map users against other logins if desired.

You can also use the ‘Auto_Fix’ action to address some other possibilities – including the ability to automatically create a login from the username specified, if you desire.

And, to get specifics on how to use these variants of sp_change_users_login should be called, you’ll want to check out books online for the specific version of SQL Server that you’re using on your destination server – though, hopefully, in most cases you won’t need to deal with this sproc as a way to re-map logins and users or roles.

And with that in mind, thanks for joining and we look forward to seeing you in subsequent SQL Server Videos as well.