Orphaned Users with Database Mirroring and Log Shipping

  • Article

Let’s start with a little background on how SQL Server logins and Database users actually work. A login (Windows/SQL authenticated) gives you the permissions to log onto a SQL Server instance. With the help of the login, an individual can access the databases on that instance using a user mapping. The only exceptions to this rule are:

a. GUEST account

b. Microsoft Windows group memberships

All other logins have the requirement that they have a mapping to a database user account. The mapping information is stored in the database along with the login name and the login SID that the database user is mapped. This information can be found out using the sys.server_principals catalog view (This gives you a list of logins created for the current instance) and sys.database_principals (This gives you a list of database users along with the principal ID and SID.)

Most organizations use Windows Authentication and after a database failover, users are able to connect seamlessly. Same is the case when the secondary database for log shipping is made the primary after a role change. When SQL Authentication is used to connect to the mirrored database after a failover or a log shipped database after a role change and the login in question has only logon rights on the destination database, then it becomes a different ballgame altogether.

SQL Server maps database users and server logins using SID values . The SID for Windows Authentications (Domain Accounts) are the same throughout the environment hence a problem never arises here when the roles (Mirror to Principal or Secondary to Primary) change. But for SQL Logins, rule of thumb tells us that the login used to login to a mirrored/secondary database should exist on both instances which hosts the principal/primary and mirror/secondary databases.

So, what happens when you use a SQL Authentication after a failover/role change??

Since, SQL Server relies on SIDs to map a database user to a SQL login. So after a failover the SIDs for a SQL Authentication is bound to have a mismatch (as SQL Authentication SIDs are machine specific and not something derived from the AD as in the case of Domain Accounts), you will start getting login failed or login timeout period expired for SQL Authentication. Some of the error messages can be of the following type:

ERROR #1:

Microsoft SQL Native Client error '80040e09'

The EXECUTE permission was denied on the object

ERROR #2:

OLE DB provider "SQLNCLI" for linked server "<server name>" returned message

"Login timeout expired".

Msg 18456, Level 14, State 1, Line 1

Login failed for user '<login name>'.

Msg 4060, Level 11, State 1, Line 1

Cannot open database "<database name>" requested by the login. The login failed.

If you look at the database user properties using Object Explorer in Management Studio, you will find that the user doesn’t correspond to a login.

image

How do I resolve this issue?

First identify the orphaned database users. This can be done using two methods:

Method 1:

Execute the script below under the context of the database in question.

select [name],SUSER_SNAME(SID) as Resolved_SID

from sys.database_principals

where type_desc = 'SQL_USER'

and SID not in (select SID from sys.server_principals)

and [name] <> 'guest'

This would give you the database user(s) that is/are orphaned and a NULL under the Resolved_SID column value in the above query output.

Method 2:

Use the sp_change_users_login stored procedure and execute the below query under the context of the database in question.

sp_change_users_login @Action='Report';

This would give you the orphaned database users along with the SID that the database user was mapped to.

How do I Workaround this issue if I cannot permanently fix this due to lack of downtime?

The Troubleshooting Orphaned Users section in SQL Server Books Online has information on how to deal with orphaned logins and fix them. However, for such situations this would be a temporary fix because the problem would raise its ugly head every time a role change or failover happens. So, if you cannot follow the steps mentioned in this post for fixing the issue permanently, use the steps mentioned in “To Resolve an Orphaned User” under the Troubleshooting Orphaned Users topic in SQL Server Books Online as a temporary solution till you get downtime to implement the permanent fix mentioned below.

How do I know that this is my actual problem?

Execute the following query on the two instances Principal/Primary and Mirror/Secondary.

select [name],SID from sys.server_principals

where type_desc = 'SQL_LOGIN'

For the logins in question, you will find that the SIDs on both the instances are different.

Now, to the how do I fix this issue permanently J

1. You will have to create the sp_help_revlogin stored procedure under master. The script is available under KB918992.

2. After the stored procedure is created, use it to script out the login(s) in question from the Principal/Primary server instance. Eg. EXEC master.dbo.sp_help_revlogin 'testuser'

3. Drop the Login(s) on the Secondary/Mirror instance. NOTE: There is a caveat here!! If the login is associated with some specific privileges and database roles, then you would have to explicitly grant/map them. The CREATE LOGIN script obtained using the sp_help_revlogin Stored Procedure doesn’t script out the permissions.

4. Re-create the Login(s) using the CREATE LOGIN scripts obtained above on the Secondary/Mirror server instance..

5. Perform a database failover/role change and re-map the SQL Authenticated Login(s) to the database user under User Mappings. This is because if you have a database user and drop the SQL Login associated with it, then the database user again becomes orphaned.

The biggest problem that you would face is mapping the database user to the newly created login. Since, the Mirrored/Secondary database is not in Read/Write mode, you cannot make changes to the database till you failover or perform a role change.

The above article applies for SQL Server 2005 & 2008 (all editions where Mirroring is offered) for Database Mirroring and SQL Server 2000, 2005 & 2008 (all editions where Log Shipping is offered) for Log Shipping.

Amit Banerjee
Technical Lead, Microsoft Sql Server

Comments

  • Anonymous
    August 18, 2009
    The comment has been removed

  • Anonymous
    August 19, 2009
    This issue only occurs when SQL Authenticated users is used in the  to connect to the mirrored database. Mirroring will work perfectly fine if before setting up mirroring it is verified that all database users mapped to SQL Authenticated logins have the same SID for both SQL instances. This is considered as a best practice as per the Database Mirroring whitepaper.

  • Anonymous
    February 04, 2013
    Alternatively we have a work around.. Let Db_A be principal and Db_B be mirror on a different server.. First of all check the Principal database for the orphaned users (using sp_change_users_login 'report') and fix them if you find any (using sp_change_users_login 'autofix' or sp_change_users_login 'update_one').. Now failover the Principla datbase.. Now Db_A is mirror and Db_B is principal.. check the mirror database db_B for orphaned users and fix if you find any.. Now failover.. Note: We may have to repeat this process every time after a new SQL Authenticated login is added to db_A which is a little tedious.. But until sql 2008R2 this is the only work around that I can think of.. Please correct me if I were wrong anywhere..