Understanding and dealing with orphaned users in a SQL Server database

Problem

Attaching and restoring databases from one server instance to another are common tasks executed by a DBA. After attaching or restoring a database, previously created users and logins in that database do not match and are known as an orphaned user.

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. How do you resolve an orphaned user?

Solution

When a database is moved from one server to another, the login ids stored in the master database do not align with the login ids stored for the database users and therefore create an orphaned user.

Without proper understanding and appropriate planning, you may face this problem. You could delete and recreate the user, but you will lose all of the configured permissions in the database. So it is necessary to have a proper linking mechanism to retain permissions which we will cover in this article.

Error Messages Associated with an Orphaned User

Some of the errors you may see for a database user 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 lose all of the configured permissions in the database. So it is necessary to have a proper linking mechanism to retain permissions.

Some more possible error messages that you may see include:

microsoft sql server management studio
sql server logins

Understanding How Orphaned Users Occur

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 mapped to the appropriate SQL Server login. There are two exceptions, the guest account and Microsoft Windows group memberships. The SQL Server logins on a server instance are visible in the sys.server_principals system catalog view and the sys.syslogins view.

On the other hand, the database user mapping information gets stored 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 for authorization purposes in the database.

Here is an example.

UsersMaping

So we can say that every time we create a SQL Server login, it appears in the sys.server_principals system catalog view or sys.syslogins views on SQL Server. The entries in the sysusers table of a database link to SQL Server logins, shown in the above picture. A column named SID creates this link.

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

Example of Creating Orphaned Users

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

Keeping this in mind, let us run some queries. Let’s see the difference between the SQL Server login SIDs (if the login is present) and the 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. 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.

Identify and Fix Orphan User in SQL Server

I have restored the AdventureWorks database from one instance to another with the four above users. Let’s run the following T-SQL command to analyze how many orphaned users there are. It produces a list of all the orphaned users. In our case, the results show that it orphaned all four users.

--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. 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 know that the SQL Server login name and the orphaned user of the database being mapped are identical, then 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 map the users to the logins, ensuring they are no longer orphaned.

If a login name does not exist, you would have to create it first before doing the mapping. A quick way is to use the following command to create the login and 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, we use the stored procedure sp_change_users_login. 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 the orphaned user. So every time a DB is attached or restored, the SID differs between SQL Server login and database user.
  • Do not use the command with Auto_Fix for linking if the Server login names mapped to a database user are different
  • A user may become orphaned also if the corresponding SQL Server login is dropped
  • Obviously, after re-linking the password of the SQL Server login, it is usable by the database user.

Next Steps

Related Posts

3 Comments

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

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

  3. /*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

Leave a Reply

Your email address will not be published. Required fields are marked *