When transferring a database to a new server using backup/restore or

detaching and re-attaching database files, the link between server logins and

database users almost invariably gets broken. Here we will take a look at why

this happens, and what we can do to fix it.

Incidentally, this article is now on it's third version! I would like to

thank all the people who wrote to me with questions and comments on the subject.

Their feedback has helped me to improve the article no end.

Where it all goes wrong

When you move a database to a new server, you only move half the information

you need to make SQL Server security work for that database on the new server,

the rest of it gets left behind.

The information that is not transferred is not transferred because it does

not live in the database you have moved; it lives in the master database

on your original server. More specifically, it lives in the syslogins

table in your master database.

Syslogins, unsurprisingly, holds login information. What catches

some people out is the fact that a login is not the same as a user.

In most software systems, the terms login and user are pretty much

interchangeable, but in SQL Server they are two different things. Here is a

quick definition:

• Login: Information that grant access to a server. It is the login ID that

  has a password associated with it, and it is the login id that people type

  in when signing on to a server.

• User: Information that grant access to a database. Database user names

  have no passwords, and people who access a database rarely have a need to

  know their user names.

When you add a login to a server, you generally grant that login access

to one or more databases. SQL Server adds an entry to the master..syslogins

table for each login, and an entry to the sysusers table in each granted

database which maps back to master..syslogins by the SUID column

in SQL Server 7 and earlier, or the SID columns in SQL 2000. (For

convenience I will stick to using the SUID column in the followng

examples)

To illustrate how this link is built, try running this query in one or more

databases.

select master..syslogins.name as login_name,

sysusers.name as user_name

from master..syslogins inner join sysusers

on master..syslogins.suid = sysusers.suid  

/* NOTE - JOIN ON "SID = SID" for SQL 2000 */

The results you will get vary from database to database and server to server,

but you should at some stage see a bunch of logins and their associated users

for that database. Note that the user and login name need not be

the same – and in one prime example they never are – the system-supplied ‘sa’

login is mapped on to the user ‘dbo’ in every database.

Example

Lets assume we are transferring a database between two servers. Server one is

called Marx and currently runs the accounts and sales databases for an imaginary

corporation. Server two is called Stooge and runs the stock database for the

same corporation. Let’s take a look at how security on these server is set up

by looking an critical parts of the sysusers and syslogins tables:

The Marx Server

All 5 Marx brothers have access to the Accounts database because they all

have a user id in the database with a valid link back to the master database syslogins

table. Only three of the Marx Brothers have access to the Sales database.

The Stooge Server

Let's assume that the aging Marx server is constantly under stress, while the

newer Stooge server has plenty of spare capacity, and we want to balance the

overall load by moving one database from Marx to Stooge. If you copy the

Accounts database to the Stooge Server, you will end up with a situation like

this:

Logins Larry, Curly and Mo now, quite incorrectly, have access to the

Accounts database because their SUIDs match up to the contents of the syslogins

table. The five Marx Brothers user IDs have been transferred to the new

server, but their logins have not. Groucho Harpo and Chico’s users are

mapped to the wrong logins, Zeppo and Gummo have no matching login at all –

their User IDs are said to be "orphaned"

To correct things we need to add logins for each Marx brother to the Stooge

server, but when we do so, their SUIDs will still not match up:

Finishing the database transfer with sp_change_users_login

To finish the database transfer properly, we need to fix the link between

accounts..sysusers and the Stooge server’s syslogins table. There is a handy

stored procedure called sp_change_users_login, which should be run in the

accounts database after all the required logins are set up. 

sp_change_users_login makes educated guesses bases on user names and login

names matching up, and changes the suid in the current database accordingly,

so we end up with something like this:

If your user names never matched your login names in the first

place, then you have a problem and you are reduced to fixing the problem

manually. Bear that in mind next time you are setting up new logins and users on

a server.

Transferring passwords to a new server

Using sp_addlogin to transfer passwords

One little known feature in SQL 7 and 2000 enables you to port a password from one server to

another without you actually knowing what the password is!

Passwords are stored in the syslogins table in encrypted form – you can

copy the encrypted password text from the original server into a sp_addlogin SQL

command to run against the new server and indicate that the password is already

encrypted using the @encryptopt = 'skip_encryption' – the login ID will then have the same password on both servers.

Use this simple script so generate sp_addlogin commands to transfer logins

to a new server.

select  'sp_addlogin @loginame = x' + name + 

        ', @passwd = "' + password + 

        '", @encryptopt = skip_encryption' +

        char(13) + 'go'

from    syslogins

where   name in ('test1', 'test2') -- include specific logins only

The example script is minimalist, you can go further and include the default

database and other security information. depending on your requirements and on

your SQL Server version.

Users of SQL Server 7 and 2000 should use @passwd =

"N' + password + in the

above script because passwords are double-byte character strings.

Upgrading between versions of SQL server

Between versions 6.5 and 7, Microsoft altered the algorithm for encrypting

passwords. This can cause you problems even if you are not moving the logins

from a 6.5 machine.

If you are generating your passwords from a SQL

Server 6.5 box and adding them to a SQL 7 or 2000 server, use the "skip_encryption_old"

option of sp_change_users_login

Some people have had difficulties with passwords

when moving SQL 7 and 2000 logins to a new server. This happens  when

passwords are stored in 6.5 format on the original server (which usually happens

during an upgrade) This article

explains how to identify and solve the problem

Using BCP or Remote Stored Procedures to transfer logins

SQL 6.5 and earlier do not support the "skip_encryption"

option, but you can still transfer logins with their passwords intact. You need

to be the sa (of course) on both your source and destination server, you

need to set the 'allow updates' server option using 'sp_configure',

and of course you need to be very careful. Back up all your databases

before attempting this.

With SQL 6.5 you have to write directly to the sylogins table in order

to transfer passwords unaltered. My preferred option is to BCP the syslogins

table from the source server, bcp it into a working table on the destination

server, and then write SQL to transfer the required logins straight into the syslogins

table, taking care not to try to copy in logins that already exist on the target

server (such as sa, probe, and and user logins that exist on both

servers. You also need to make sure that SUIDs are not duplicated, and I

usually do this by adding an arbitrary number to the SUIDs from the

source server after transferring the login details into the working table.

About the author

Neil Boyle is an independent SQL Server consultant working out of London,

England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk