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
on your original server. More specifically, it lives in the
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
- 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
to one or more databases. SQL Server adds an entry to the
table for each login, and an entry to the
sysusers table in each granted
database which maps back to
master..syslogins by the
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
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
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.
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
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
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
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
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
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
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
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
"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
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
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
table, taking care not to try to copy in logins that already exist on the target
server (such as
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.