Echoes from the field 2: Much ado about logins and SIDs

Echoes from the field 2: Much ado about logins and SIDs

It’s really common to see questions in the newsgroups about the inability to access a database that has been moved between servers. Invariably, the problem is that a SQL Server login (i.e., not a Windows login) can’t access the database.

By default, when you recreate a SQL Server login, 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. That’s often the point at which the administrator wants to throw their mouse through the screen but it doesn’t have to be a problem.

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 = N'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 recommended 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, a better 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:

  1. A database is restored from another server (or a reinstalled server).
  2. 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)

Then we could have recreated the logon with the required SID specified:

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

In the meantime, we’ve added a tool to our SDU Tools collection to help with this: CreateSQLLoginWithSIDFromDB. You’ll find details here: SDU Tools .

2025-10-25