Fixing Broken Logins and Transferring Passwords

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

master..syslogins Accounts..sysuers Sales.sysusers
Suid Name Suid name Suid Name
1 Sa 1 Dbo 1 Dbo
10 Groucho 10 Groucho 10 Groucho
11 Harpo 11 Harpo 13 Zeppo
12 Chico 12 Chico 14 Gummo
13 Zeppo 13 Zeppo    
14 Gummo 14 Gummo    

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

Stooge.Master..syslogins Stock..sysuers
Suid name Suid Name
1 Sa 1 Dbo
10 Larry 10 Larry
11 Curly 11 Curly
12 Moe 12 Moe

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:

Stooge.master..syslogins Stock..sysuers Accounts..sysuers
Suid Name Suid name Suid name
1 Sa 1 Dbo 1 Dbo
10 Larry 10 Larry 10 Groucho
11 Curly 11 Curly 11 Harpo
12 Moe 12 Moe 12 Chico
        13 Zeppo
        14 Gummo

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:

Stooge.master..syslogins Stock..sysuers Accounts..sysuers
Suid Name Suid name Suid Name
1 Sa 1 Dbo 1 Dbo
10 Larry 10 Larry 10 Groucho
11 Curly 11 Curly 11 Harpo
12 Moe 12 Moe 12 Chico
13 Groucho     13 Zeppo
14 Harpo     14 Gummo
15 Chico        
16 Zeppo        
17 Gummo        

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:

Suid Name Suid name Suid name
1 Sa 1 Dbo 1 Dbo
10 Larry 10 Larry 13 Groucho
11 Curly 11 Curly 14 Harpo
12 Moe 12 Moe 15 Chico
13 Groucho     16 Zeppo
14 Harpo     17 Gummo
15 Chico        
16 Zeppo        
17 Gummo        

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 p_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.

Neil Boyle
Neil Boyle
Neil Boyle left school at the age of sixteen thinking that computers were things that only existed in Star Trek. After failed careers as a Diesel Mechanic, Industrial Cleaner, Barman and Bulldozer Driver he went back to college to complete his education. Since graduating from North Staffs Poly he has worked up through the ranks from Trainee COBOL Programmer to SQL Server Consultant, a role in which he has specialised for the past seven years.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles