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