Understanding and dealing with orphaned users in a SQL Server database

By:   |   Comments (13)   |   Related: > Security


Problem

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring of a database, previously created and configured logins in that database do not provide access. The most common symptoms of this problem are that the application may face login failed errors or you may get a message like the user already exists in the current database when you try to add the login to the database. This is a common scenario when performing an attach or a restore, so how do you resolve this?

Solution

When a database is moved from one server to another server the login ids stored in the master database do not align with the login ids stored in each user database. As mentioned above some of the errors you may see after attaching or restoring a database include:

Msg 229, Level 14, State 1
%s permission denied on object %.*s, database %.*s, owner %.*s

or

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database.

Without proper understanding and appropriate planning you may face this problem. You could delete and recreate the user, but you will loose all of the configured permissions. So a proper linking mechanism is required, so that permissions are retained.

Some more possible error messages that you may see include

microsoft sql server management studio

sql server logins

Before getting to the solution for this problem, it would be better to have a glance at the backend issue. SQL Server logins, which are stored in the master database are mapped against individual databases. SQL Server logins access individual databases using a database user that is mapped to the appropriate SQL Server login. There are two exceptions, the guest account and Microsoft Windows group memberships. The SQL Server 2005 logins on a server instance are visible in the sys.server_principals system catalog view and the sys.syslogins view. For SQL Server 2000 you may get SQL Server login information in sysxlogins table.

On the other hand the mapping information to a database user is stored within the database in the system table sysusers. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization purposes in the database.

UsersMaping

So we can say that every time we create a SQL Server login, it will be seen in the sys.server_principals system catalog view or sys.syslogins views on SQL Server 2005 or in the sysxlogins table for SQL Server 2000. The entries in the sysusers table of a database are linked to SQL Server logins shown in the above picture. This link is created through a column named SID.

If we move our database to another SQL Server instance through any process, the new server might or might not have the same logins and the SIDs of these logins would probably be different from the SIDs of these logins in the original server. What this means is that, the sysusers table in the moved database has SIDs that are not matched with the login info in the master database on this new server. Therefore we get orphaned users.

As an example, I have created and configured four users with permissions in the AdventureWorks database. The users are TestUser1, TestUser2, TestUser3 and TestUser4. When I restored the backup of this database onto another SQL Server 2005 instance, although the users were present in the AdventureWorks database and the logins existed on the new server, but none of these logins had access to the newly restored database.

So keeping this scenario in mind, let us run some queries to see the difference between the SQL Server login SIDs (if SQL Server login is present) and database user SID for TestUser3. 

--Script to view difference in SID

USE MASTER
GO 
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'TestUser3'
GO

USE AdventureWorks
GO 
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'TestUser3'
GO

The result below shows the SID of the SQL Server login and that of the user database id differ and this is what causes the problem.

sql server login and used database differ

Now that we have a better understanding of the problem, it is time to get to some useful commands for analysis and a solution.

I have restored the AdventureWorks database from one instance to another with the four above users. Now to analyze how many orphaned users there are in my restored database, I will run the following T-SQL command which produces a listing of all the orphaned users and in our case all four users are orphaned.

--Command to generate list of orphaned users

USE adventureWorks
GO

sp_change_users_login @Action='Report'
GO

command to generate orphaned users

Now that we have the list of the orphaned users we can begin to fix the problem. To overcome this problem, you need to link the SIDs of the users (from sysusers) to the SIDs of the valid logins in the master database. The following command remaps the server login account specified by TestUser1 with the database user specified by TestUser1.

--Command to map an orphaned user

USE AdventureWorks
GO

sp_change_users_login @Action='update_one', 
@UserNamePattern='TestUser1', 
@LoginName='TestUser1'
GO

Or if you are sure that SQL Server login name is the same as the orphaned user of database being mapped, then you may use a shorter command such as this for TestUser2..

--Command to map an orphaned user

EXEC sp_change_users_login 'Auto_Fix', 'TestUser2'
GO

Both commands will map the users to the logins and they will be not orphaned any longer.

If a login name does not exists, you would have to create it first before doing the mapping. A quick way to do this is to use the following command which will create the login and then map the login to the user.

--Command to map an orphaned user to a login that is not present but will be created

EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'
GO

Summarizing the T-SQL Used

In the above process, the stored procedure sp_change_users_login is used. The variable [ @Action ] specifies the exact use of this stored procedure. It accepts a parameter as varchar(10) and can have one of the following values:

  • If parameter is Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present.
  • If parameter is Report, it lists the orphaned users and their security identifiers (SID).
  • If parameter is Update_One, it links the specified database user to an existing SQL Server login.

Some considerations

  • sp_change_users_login requires membership in the db_owner fixed database role. Only members of the sysadmin fixed server role can specify the Auto_Fix option.
  • When the orphaned user is mapped, the SID in the master database is allotted to orphaned user, so every time a DB is attached or restored the SID will differ between SQL Server login and database user.
  • If you have different Server login names mapped to a database user then do not use the command with Auto_Fix for linking
  • A user may become orphaned also if the corresponding SQL Server login is dropped
  • Although obvious, but it is good to mention, after re-linking the password of SQL Server login can be used by the database user.
Next Steps
  • Now that you have a better understanding of this issue and how to resolve it using sp_change_users_login, it would be a good practice to prepare a script for detection and linkage of orphaned users for all of your databases.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, February 18, 2021 - 1:02:29 AM - Manish Back To Top (88257)
/*fix orphan users in one particular database */

declare @query varchar(1000)
declare @executequery cursor
set @executequery=cursor for
select ' sp_change_users_login '+CHAR(39)+'update_one'+CHAR(39)
+','+CHAR(39)+name+CHAR(39)+','+CHAR(39)+name+CHAR(39)
from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
open @executequery
fetch next from @executequery into @query
while @@fetch_status=0
begin
exec (@query)
print (@query)
fetch next from @executequery into @query
end
close @executequery;
deallocate @executequery;

/*fix orphan users in all databases except system databases */



DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(2000)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM sys.databases where database_id>4
and name not like '%master%'

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Command ='
use '+ @DB_Name+';
declare @query varchar(1000)
declare @executequery cursor
set @executequery=cursor for
select '' sp_change_users_login ''+CHAR(39)+''update_one''+CHAR(39)+'',''+CHAR(39)+name+CHAR(39)+'',''+CHAR(39)+name+CHAR(39)
from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
open @executequery
fetch next from @executequery into @query
while @@fetch_status=0
begin
exec (@query)
print (@query)
fetch next from @executequery into @query
end
close @executequery;
deallocate @executequery;
go'

print @Command

FETCH NEXT FROM database_cursor INTO @DB_Name
END

Saturday, August 9, 2014 - 11:36:13 AM - Atif Shehzad Back To Top (34072)

@Nasar. The process mentioned in this article does not work for AD logins. Orphaned users issue occur due to differences of login and user SID. I think this issue will not occur in case of AD logins and these SID will be stored in AD and will be same on all servers where AD login is added.

Wednesday, August 6, 2014 - 12:10:08 PM - Nasar Azam Back To Top (34029)

Atif: Does this work for Active directory Logins?  I have not found a way to sync orphaned users with AD logins.  I thought your technique only works for SQL Server logins as opposed to windows authenticated Logins.  Please comment.

 

thanks

 

Nasar

Monday, July 14, 2014 - 12:48:22 AM - Atif Shehzad Back To Top (32685)

@Chethan. For your mentioned scenario there will be no orphaned users issue because SIDs of DB user and server login will be same. However if between 12 AM and 2:30 AM login is recreated then SIds will differ and orphaned user will be there for that login.

Sunday, July 13, 2014 - 1:28:27 PM - chethan Back To Top (32679)

Thank you fro the detailed explaination.

As per the above explaination,the orphan users will exist only when the databases are restored on to the other.
I want to know whether the orphan users exist when the database backup is restored on the same server

for eg: server 'A', database 'B',
Database 'B' backup taken on 12:00am and it has been restored back at 2:30am on server A,there were no activities in this period.
Does this restoration cause any orphan users on Database 'B'?

Friday, July 11, 2014 - 6:21:32 AM - Olu Back To Top (32652)

Thanks for such a brilliant and helpful article, I'll be keeping it for future ref,

Monday, May 13, 2013 - 1:29:26 PM - avs Back To Top (23925)

If none of the things worked, then just drop the user from the database (Not from the Server) and map the user wih the database it will work, this is an issue in SQL Server where user SID is different from Master, if it can't be fixed.

 

Wednesday, January 9, 2013 - 7:18:50 PM - Gamal Al Kawally Back To Top (21358)

It's really great, clear and very usefull article that resolve my access problems after restoring the database.thank you so much.

Friday, August 10, 2012 - 2:07:44 AM - Atif Back To Top (18986)

@leonid. In that caseyou may use following command to set sa as db owner

EXEC sp_changedbowner 'sa'

Regards

 

Thursday, August 9, 2012 - 7:48:04 AM - Leonid Brodski Back To Top (18974)

Thanks for the article, but what do I need to do if the dbo user is orphaned? The sp_change_users_login procedure is not allowed to alter it.

Tuesday, April 12, 2011 - 1:28:35 AM - Atif Shehzad Back To Top (13594)

@krish, Thanks for your appreciation. I would try to produce quality content of this kind.

Regards

Monday, April 11, 2011 - 9:59:30 AM - Krish Back To Top (13583)

It's really good and very usefull article.I am expecting more articles from you of this kind.thank you so much.

Wednesday, September 24, 2008 - 1:02:41 PM - adil aziz Back To Top (1868)

Salam Atif, It was really very pleaseure and great feelings after reading your article here on this site. My name is adil and i work as as Sql Server Dba in Usa. I also used to work in NADRA back in 2000 as DEO in islamabad.

Thanks..

 

Adil

 














get free sql tips
agree to terms