Much ado about logins and SIDs

Some time back I posted about Logins and SIDs and I’ve had a number of people ask me for examples. So I’ve updated the post here.

A very common issue that’s raised in the newsgroups relates to SQL Server logins that need to be moved between servers. When you recreate a SQL Server login (ie: not a Windows one), by default you get a new security ID (SID), even though you have the same user name and password.

The problem then comes when you restore a database from another server. You can’t access it. If you try to create the user entry in the database, it says it already exists and fails. But if you try to list the users in the database, it also doesn’t show it.

Let’s try an example: First I’ll create a database and a login and add the user to the database:

CREATE DATABASE LoginTest;
GO

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';
GO

USE LoginTest;
GO

CREATE USER GregTest FROM LOGIN GregTest;
GO

USE master;
GO

Next we’ll detach the database and drop and recreate the login:

EXEC sp_detach_db 'LoginTest';
GO

DROP LOGIN GregTest;
GO

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';
GO

If we reattach the database, we now have the situation where the new login has a different SID to the one that the user has in the database, even though the user has the same name. This is very much like what happens when you restore a database on another server and recreate the login there:

CREATE DATABASE LoginTest
ON (FILENAME = 'C:\\SQLData\\Data\\LoginTest.mdf')
FOR ATTACH;
GO

If we try to use the login to access the database, it won’t work. If we try to create the login, it won’t work either:

USE LoginTest;
GO

CREATE USER GregTest FOR LOGIN GregTest;
GO

Msg 15023, Level 16, State 1, Line 1 User, group, or role ‘GregTest’ already exists in the current database.

The standard answer for this has been to use sp_change_users_login. It has an option to list any mismatched logins and database users ie: those with the same names but different SIDs.

EXEC sp_change_users_login 'Report';
GO

UserName    UserSID -——————————————— GregTest    0x5D5F9089AFE1D4428106DE1B52BE0DFC

It then has an option to “fix” it. The way it fixes it is to update the SID in the database user to match the login:

EXEC sp_change_users_login 'Update_One', 'GregTest', 'GregTest';
GO

In Service Pack 2 of SQL Server 2005, new syntax was introduced to deal with this:

ALTER USER GregTest WITH LOGIN = GregTest;
GO

The problem I see with this all is that it still just temporarily fixes the problem or at worst, propagates it to other servers. It’s not the database SID that needs fixing, it’s the Login’s SID. If the Login’s SID was correct, there wouldn’t be a problem with copying the databases around. The most common scenario I see is the following:

  • A database is restored from another server (or a reinstalled server).
  • The logins that use the database need to be recreated.

I’ve gotten around this problem in the past by specifying the SID value when creating the login in TSQL. It is an optional parameter. If you provide the same value as on the other server, you don’t have the problem. For example, instead of executing sp_change_users_login or ALTER USER above, we could have done the following:

USE LoginTest;
GO

SELECT sid FROM sysusers WHERE name = 'GregTest';
GO

sid -———————————- 0x5D5F9089AFE1D4428106DE1B52BE0DFC (1 row(s) affected)

What we could then have done was:

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',
  SID = 0x5D5F9089AFE1D4428106DE1B52BE0DFC;
GO

The upside of this is that it’s a permanent fix. Next time you restore the database, you won’t have to fix it again.

Because this is such a common issue, I’d really like to see it directly supported in T-SQL, such as:

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',
  SID FROM DATABASE LoginTest;
GO

This would avoid the problem in the first place and then give you a database you could copy around as needed.

2009-02-02