Moving databases with Users, Logins, Passwords, and SIDs

Submitted by Corey Bunch

Introduction

To move a database from one server is relatively simple, as most of us know. You can either use detach & reattach, or backup then restore. This is true if there are very few users and logins associated with that database. A database containing hundreds of users is another story, however. Especially one that may be mission critical to your business or organization. Imagine how mad your helpdesk manager would be when receiving 250 user complaints that none of their passwords worked because you've orphaned their database user from the server login in the move. There have been articles written dealing with quickly moving databases, and articles about just moving logins. This article deals with moving both the database AND the affected users, along with all their information for that one particular database that you may be interested in moving to a new server.

What's the point?

Between SQL7 servers, the DTS "transfer logins" task transferred logins and users, but no passwords. From SQL7 to SQL2000, or between SQL2000 servers, the DTS "transfer logins" transfers users, logins, and passwords, but they are all orphaned from the original SID (Security Identifier), which means the DBA has to go back and remap all the database users to the server logins. Uggggghhhhhh.....What's the point?

Below we'll take a look at how to get your database, the associated users, and ALL the information needed to make things flow smoothly for not only your users, but you too. This includes their passwords, server login, and SID. For the purposes of this article, the originating server will be called ServerA, and the destination server will be called ServerB.

First Step - Backup

First and foremost, take your final full backup of the database on ServerA. Use a script similar to below. The STATS keyword will just show a progress indicator. This is useful for large databases.

BACKUP DATABASE DummyDatabase
TO DISK = '\\Networkshare\DummyDatabase_dump.BAK'
WITH  INIT,
NAME = ‘DummyDatabase’,
DESCRIPTION = ' DummyDatabase INIT BackupFull',
STATS=1
RESTORE VERIFYONLY FROM DISK = '\\Networkshare\DummyDatabase_dump.BAK' WITH STATS=1

Copy this backup file to a location ServerB that will be accessible to SQL Server on ServerB. Don't restore it yet, just copy the backup file over.

Next Step - Get logins

LOAD AND ALTER SCRIPTS

Load the script found at this Microsoft page (KB246133) while logged in to the master database on ServerA. This will create two stored procedures in your master database, called sp_hexadecimal and sp_help_revlogin. We will end up executing sp_help_revlogin, which in turn, uses sp_hexadecimal.

If you're only concerned about one database, you don't need all the server logins. To restrict your sp_help_revlogin results to only one database, then simply alter the sp_help_revlogin stored procedure. First you’ll need to know the dbid of the database you’re interested in. You can find this out by running the below select statement in master.

SELECT * FROM sysdatabases

Find the dbid of the database you’re interested in. We’ll use dbid = 5 for this example. Now you can alter the sp_help_revlogin procedure to only give you logins for that particular database. Note the addition to the where clauses below.

ALTER PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name  'sa' and dbid = 5
ELSE

  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name and dbid = 5
.................

RUN SP_HELP_REVLOGIN

After altering the procedure, run it. The output of the sp_help_revlogin procedure includes a list of logins, and a good amount of cryptic looking material. An important thing to consider here is if any of these logins coming from ServerA already exist in ServerB. If they do, you’ll need to not include these users. For instance, if the "testuser2" login below already exists on ServerB, then you’ll need to get rid of the "testuser2" section. Otherwise, you’ll receive a "server login already exists" error message. Also, you’ll need to alert these users to use their existing ServerB password for this migrated database from ServerA.

Run the output from the sp_help_revlogin script from ServerA on ServerB. This will create the server logins, SIDS, and bring over the passwords.

Note

It is likely, although not guaranteed, that these users will need to have the default database set on ServerB, depending on the application settings. If you want the users default database settings also carried over, plan on executing the sp_defaultdb stored procedure later. You’ll need the list of logins. If the list is large, you can use various text editors, or even MS Excel to generate repeating scripts, such as below....

EXEC master..sp_defaultdb 'testuser', 'DummyDatabase'
EXEC master..sp_defaultdb 'testuser2', 'DummyDatabase'
.......................

Final Step - Restore

Now it is time to restore your database to ServerB. Use a script similar to below.....Note the "WITH MOVE" parameters, in case the files are stored in a different location on ServerB than they were on ServerA.

RESTORE DATABASE DummyDatabase
FROM DISK = 'R:\DummyDatabase_dump.bak'
WITH
MOVE 'Dummy_data'
TO 'R:\Program Files\Microsoft SQL Server\MSSQL\Data\DummyDatabase_data.mdf',
MOVE 'Dummy_Log' 

TO 'R:\Program Files\Microsoft SQL Server\MSSQL\Data\DummyDatabase_log.ldf',
STATS=1

After successful restoration of the database onto ServerB, browse to "Users" for the database. All of your users should be mapped to the logins. If not, clean up any orphaned users and remap to the appropriate server login.

Conclusions

Moving databases, users, logins, and passwords from server to server is a bit of a pain, yes. But hopefully not as big of a pain now that you have this procedure to follow!