Fixing Broken Logins and Transferring Passwords
August 4, 2000
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
When you add a
To illustrate how this link is built, try running this query in one or more databases.
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
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
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
To correct things we need to add
Finishing the database transfer with
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.
sp_addloginto 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.
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
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.