Understanding and dealing with orphaned users in a SQL Server database

By:   |   Updated: 2008-09-24   |   Comments (15)   |   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


Article Last Updated: 2008-09-24

Comments For This Article




Tuesday, October 15, 2024 - 11:46:56 AM - CP Patel Back To Top (92569)
DECLARE @DatabaseName nvarchar(255)
DECLARE @UserName nvarchar(255)
DECLARE @Command nvarchar(1000)
DECLARE @SqlStatement nvarchar(4000)

IF OBJECT_ID( 'tempdb..#temp') IS NOT NULL
DROP TABLE tempdb..#temp

CREATE TABLE tempdb..#temp (name VARCHAR(100))

DECLARE database_cur CURSOR FOR
SELECT sd.name FROM sys.databases sd
INNER JOIN (SELECT database_id,collation_name FROM sys.databases WHERE name='master') sd1
ON sd.collation_name=sd1.collation_name
AND sd.database_id >4
WHERE sd.user_access=0 AND sd.is_read_only=0
AND sd.state_desc='ONLINE'

OPEN database_cur
FETCH NEXT FROM database_cur INTO @DatabaseName

WHILE (@@FETCH_STATUS=0)
BEGIN
SELECT @Command='
INSERT INTO #temp
SELECT UserName = su.name
FROM '+quotename(@DatabaseName)+'..sysusers su
JOIN sys.server_principals sp ON sp.name = su.name COLLATE Latin1_General_CI_AS --Please update the Collation here
WHERE issqluser = 1 AND
(su.sid IS NOT NULL AND su.sid <> 0x0) AND
suser_sname(su.sid) is null
ORDER BY su.name'

EXEC sp_executesql @command

DECLARE orphan_user_cur CURSOR FOR
SELECT name FROM #temp WHERE name IS NOT NULL

IF @@ROWCOUNT=0
BEGIN
PRINT 'No Orphan User to be fixed for '+@DatabaseName
END

OPEN orphan_user_cur
FETCH NEXT FROM orphan_user_cur INTO @UserName

WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @UserName + 'Orphan User Name Is Being Resynced'
EXEC sp_change_users_login 'Auto_Fix', @UserName
--EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphan_user_cur INTO @UserName
END

CLOSE orphan_user_cur
DEALLOCATE orphan_user_cur

TRUNCATE TABLE #temp

FETCH NEXT FROM database_cur INTO @DatabaseName
END

CLOSE database_cur
DEALLOCATE database_cur

Sunday, June 16, 2024 - 3:22:03 AM - Albert Back To Top (92313)
Hi,

add join statement (SQL Server Login user[sys.syslogins] & Database users[sys.sysusers])

select ' sp_change_users_login '+CHAR(39)+'update_one'+CHAR(39)+','+CHAR(39)+S01.name+CHAR(39)+','+CHAR(39)+S01.name+CHAR(39)
from sys.sysusers S01
inner join sys.syslogins S02
on (S01.name COLLATE Korean_Wansung_CI_AS = S02.name)
where S01.issqluser = 1 and (S01.sid is not null and S01.sid <> 0x0) AND SUSER_SNAME(S01.sid) IS NULL

Thanks, Manish

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