Migrating Logins from One SQL Server to Another

As
part of the normal work, a Database Administrator (DBA) will be required to migrate
databases between servers. One of the reasons for a migration might be that
you are moving an application from a quality assurance (QA) environment to a
production environment. Another reason might be your current database server
hardware has reached the replacement date and you need to migrate your
databases from the current, out-dated server to a new server. Yet another
reason might be you are migrating your application from SQL Server 7.0 to SQL
Server 2000. Whatever the reason you will more than likely have to deal with migrating
not only the data, but the SQL Server logins that access that data as well.

There
are a number of different ways to migrate SQL Server logins. You can manually
re-enter the entire existing login on the new server. You can use DTS to
transfer logins. There are probably a number of other ways to transfer
logins. This article will discuss one of those other ways to streamline the
migration of SQL Server logins, by using the stored procedure (SP) sp_help_revlogin.

What
does the sp_help_revlogin do?

The
sp_help_revlogin SP is a Microsoft provided utility that generates a TSQL
script to migrate logins from one server to another. This SP will not only
copy the existing logins, but it will also copy the passwords and Security
Identification Numbers (SID) associated with SQL Server Authenticated users.

Why
you might want to use sp_help_revlogin

When
you move a database from one server to another, the entire database and all the
system tables associated with it are also moved. One of those system tables is
the sysusers table. The sysusers table contains all the users, groups and
roles that have access to the database. In order for a person to be able to
access the database, they must have two things. The first thing they must have
is a SQL Server login. The second thing they need is to be defined as a user
in the database. Therefore, if you copy a database from one server to another
and the users of the database don’t have SQL Server logins then these database users
become orphan users. An orphan user is a user in a database with a SID that
does not exist in the syslogins table in the master database. Also if the SID
stored in the database sysusers table, differs from SID stored in the syslogin
table for the matching database user, then the database user is also considered
an orphan user. If you retain the original SID for logins, when a user
database is migrated, you will not have a problem with orphan users.


By
using the sp_help_revlogin SP, you can move logins from one server, and create
the login entries in the new server and retain the sid. By making sure that all
logins exist on the new server prior to copying a database, you will ensure there
will be no orphan users after you complete the database copy The sp_help_revlogin
SP helps DBA’s quickly move logins from
one server to another.

How
the sp_help_revlogin Works

The
process of moving logins requires not only the sp_help_revlogin SP, but also
the sp_hexidecimal SP. These scripts and more information about migrating
logons can be found in the following Microsoft Knowledge Base article http://support.microsoft.com/default.aspx?scid=kb;en-us;246133.
I have also included a script to create both of these SP’s at the bottom of this article. Let’s review the sp_help_revlogin process.

The
easiest way to execute the sp_help_revlogin script is via Query Analyzer. The
SP accepts a single optional parameter, @login_name. If you only want to
generate a single login, then you can pass a valid login to sp_help_revlogin,
and it will generate a script to create the specified login. When @login_name
is not specified this SP generates a TSQL script to create all the logins on
the server.

Based
on whether the @login_name was specified, the sp_help_revlogin SP builds a
cursor called login_curs that contains information in the sysxlogins table in
the master database for a single login or all the logins on the server, except sa’. The
columns placed in the cursor are sid, name, xstatus and password. The xstatus
column will be used to determine if the login is a Windows or SQL Server
Authenticated users, as well as whether the login has been denied access. The sid
and password will be used to make sure the new login on the new server retains
the same sid and password as the original server.

Next
the sp_help_revlogin SP processes through the login_curs cursor one login at a
time in a while loop. Inside the while loop the SP determines if the login is
a Windows authenticated user or group (xstatus = 4) or not. If the user is a
Windows authenticated user then a sp_grantlogin statement is generated. For
the SQL Server authenticated users a sp_addlogin statement, with the skip_encryption
parameter is generated. The skip_encryption parameter is used on the
sp_addlogin statement to tell SQL Sever that an encrypted password is being
specified when the login is being created, so the password should not be
encryted.

For
SQL Server authenticated users, the SP needs to determine the sid and password
of the login. Since the sid and the password as stored as binary numbers, the sid
and password values need to be converted to a string that contains the hexadecimal
representation of the binary number. The sp_hexadecimal SP is used to do this
binary number to hex string conversion.

When
the sp_help_revlogin execution is complete, the script to create the logins
should be displayed in the Query Analyzer results pane. All you need to do to
add these logins to a server would be just to copy the script from the results
pane and run it against the new server where you want to add the logins.

Other
uses for the sp_help_revlogin SP

Using
the sp_help_revlogin as it comes from Microsoft will move all logins or a
single login. Maybe you only want move all the logins for a single database.
It is easy to modify the code that creates the “login_curs” to identify only
the users you want to move.

Often
I am only moving a single database. When I do that, I modify the creation of
the “login_curs” cursor command to look like so:


SELECT sid, name, xstatus, password FROM master..sysxlogins a join
Your_DB..sysusers b on a.sid = b.sid
WHERE srvid IS NULL AND name <> ‘sa’

By
using this logic instead, the sp_help_revlogin will only generate
sp_grantlogin, and sp_addlogins for users of the database Your_DB.

Another
possible use might be to generate the script for all logins, then delete the
logins you do not want to move. This method allows you to be selective in the
logins that will be created on the new server.

Conclusion

Here I have shown you a method to quickly generate a script that will move all
logins from one server to other, as well as offered you ideas on how to move a
sub set of users. I am sure you can find other uses for this sp. Using
sp_help_revlogin is much easier than re-entering all logins on a new server. In
addition, re-entering logins would most likely establish a new password for
each login moved, where as this sp allows you to move the passwords. Moving
the passwords from one server to another is more user friendly. SQL Server
users will be grateful that they do not have to remember a new password, and it
makes the migration a little more transparent to the SQL Server users.

Script to Create
sp_hexidecimal and sp_help_revlogin

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles