Introduction

An SQL Server login identifies its corresponding database user(s) by security identifiers (SID) ^[1]. SID discrepancy may occur when a database is restored from a source instance to a destination instance. User(s) in the restored database, inherited from those in the source database, may not associate with any login in the residing destination instance. A database user, without a corresponding login in the residing instance, is called orphaned user. In this status, application(s) cannot access the database with this user, as the user lacks a corresponding login to represent it in the instance. Several articles have described the technical details of how to resolve this SID discrepancy or orphaned user issue in read-write databases ^{[2, 3]}. In this technical note, we will address how to probe and resolve SID discrepancy in a read-only database.

Scenario

A reporting application routinely accesses a read-write database via a designated application user (prodreport) in a reporting database instance (PRODRPT). Due to a reporting feature enhancement, this application now needs to access a read-only database (CorpSales) in the instance as well. CorpSales is a secondary database via log shipping from a corresponding production database instance (PRODSVR).

The User prodreport must be added to the CorpSales database in PRODRPT as one of the enhancement requirements. However, a user cannot be directly created in a read-only database. The work-around, in this case, is to create a corresponding login and user pair in the production instance. This newly-created user is then transferred to the CorpSales in the PRODRPT instance, through log shipping.

Although the desired user now exists in CorpSales database of the PRODRPT instance, connection to the CorpSales database still fails. When trying to query data from CorpSales database, an error message arises as shown below.

-- User prodreport fails to access read-only CorpSales in PRODRPT
USE CorpSales;
GO
SELECT TOP 1 * FROM CorpSales.dbo.Corp_Account;
GO
-- Resulting error message

This error message implies that prodreport is a valid login, but the user prodreport is not. The Query engine fails to access the CorpSales database as the security context of the login differs from the user’s.

Troubleshoot

In SQL Server, a user identifies its login, vise versa, by security identifier (SID). SQL Server provides an excellent native tool, SP_CHANGE_USERS_LOGIN, to examine any possible SID discrepancy in a database in question ^[4]. Now, let us run a user SID discrepancy report against the read-only database CorpSales:

-- Get orphaned user and its SID
USE CorpSale;
GO
EXEC SP_CHANGE_USERS_LOGIN 'report';
GO
-- Query output

This report returns one row output that indicates an orphaned user existence in the database. The first output value is the orphaned user name and the second is the SID of this orphaned user. For example, the above output shows orphaned user prodreport with an SID “0x2F16A7C2D3A19E4786FB90C455D73E2B”. If no orphaned user exists in the database, no output is returned.

How can we prove the association (or disassociation) of a login with a user in a database instance? Let us examine the SIDs of login and user prodreport in the PRODRPT instance with executing below query.

-- Get SIDs of the login and user prodreport in PRODRPT instance
USE master;
GO
SELECT l.name AS LoginName, l.sid AS LoginSID
 ,u.name AS UserName, u.sid AS UserSID
 FROM dbo.syslogins l
 JOIN CorpSales.dbo.sysusers u ON l.name = u.name
 WHERE l.name = 'prodreport';
GO
-- Query output: SIDs for the login and user prodreport

The query output shows that login prodreport’s SID is “0x2F16A7C2D3A19E4786FB90C455D73E2A”, while the prodreport’s SID is “0x2F16A7C2D3A19E4786FB90C455D73E2B”, in the PRODRPT instance. This SID discrepancy suggests that 1) no relationship exists between user prodreport and login prodreport, 2) prodreport is an orphaned user, and 3) no other login in the PRODRPT instance associates with this user (see above SID report). Since user prodreport lacks a valid login association in the PRODRPT instance, user prodreport possesses no active roles or permissions in the database as it does in the PRODSVR instance, when looking through SSMS.

With this orphaned user’s SID in mind, let us further check user prodreport’s SID in the PRODSVR instance by running the following query:

-- Get prodreport SIDs in PRODSVR instance
USE master;
GO
SELECT l.name AS LoginName, l.sid AS LoginSID
 ,u.name AS UserName, u.sid AS UserSID
 FROM dbo.syslogins l JOIN CorpSale.dbo.sysusers u
 ON l.sid = u.sid AND l.name = u.name
 WHERE l.name = 'prodreport';
GO
-- Query output: prodreport SIDs in PRODSVR instance

The query result above shows identical SID for prodreport login and user in the PRODSVR instance. This unique SID serves as the security context of this login and user pair. Now let us review all the previous SID probing results shown in Table 1.

Table 1. Prodreport SID Probe in PRODSVR and PRODRPT Instances

Data in Table 1 suggests that 1) prodreport is an orphaned user PRODRPT.CorpSales, because its SID does not match any login’s SID, and 2) this orphaned user is originated in source instance PRODSVR, because its SID perfectly matches the SIDs of the both login and user prodreports in PRODSVR. In addition, this probing reveals that identifying the association of a database user with a login is their unique SID, rather than their physical names.

Resolution

Resolving orphaned user issue requires SID synchronization of perspective login and user pair in an instance. This is easy in a read-write database. For example, executing SP_CHANGE_USERS_LOGIN shown below will synchronize the orphaned user’s SID with that of the corresponding login in the instance and fix the orphaned user issue.

-- Fix the orphaned user issue in a read-write database
USE CorpSale;
GO
EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE','prodreport','prodreport';
GO

On the contrary, fixing the orphaned user issue in a read-only database takes an opposite approach to synchronize the SID, since the target database is not writeable. In practice, this fix involves dropping the relevant login (if it already exists in the residing instance), and recreate it with the orphaned user’s SID. Once the login is recreated, the user has a valid login to associate with in the residing instance and thus becomes a fully functional and normal user.

The below procedure illustrates how to resolve this orphaned user issue in the CorpSales database in PRODRPT instance.

1. Drop prodreport users from all databases that associate with login prodreport, as needed.
2. Drop login prodreport.
3. Retrieve SID of the orphaned user prodreport from CorpSales database.

-- Get the orphaned user’s SID in read-only CorpSales database
USE CorpSales;
GO
SELECT name AS UserName, SIDAS UserSID
 FROM dbo.sysusers
 WHERE name = 'prodreport';
GO
-- Query output: SID of orphaned prodreport

4). Recreate login prodreport with the orphaned user’s SID retrieved in Step 3.

-- Recreate the login prodreport with the orphaned user’s SID
USE master;
GO
CREATE LOGIN prodreport
 WITH PASSWORD = 'SecretPassword'
 ,SID = 0x2F16A7C2D3A19E4786FB90C455D73E2B
 ,DEFAULT_DATABASE = master
 ,CHECK_EXPIRATION = OFF
 ,CHECK_POLICY = OFF;
GO

5). Run below query to verify the SID synchronization between login prodreport and user prodreport in CorpSales (The SIDs of this login and user pair should be synchronized now).

-- Verify SID synchronization of the login and user pair

USE master;
GO
SELECT l.name AS LoginName, l.sid AS LoginSID
 ,u.name AS UserName, u.sid AS UserSID
 FROM dbo.syslogins l JOIN CorpSales.dbo.sysusers u
 ON l.sid = u.sid AND l.name = u.name
 WHERE l.name = 'prodreport';
GO
 -- Query output: SID synchronized in PRODRPT instance

6). Recreate the users and restore the proper user rights in the appropriate databases (reverse Step 1) as needed.

Verification

Finally, exec SP_CHANGE_USERS_LOGIN 'report' against the CorpSales and prodreport should be no longer an orphened user. Next, run the report application. If the report returns desired data, the SID discrepancy in the read-only CorpSales database is resolved.

References

1. Edward Whalen, et al., SQL Server 2005 Administrator’s

Companion, Chapter 16, 2007, Microsoft Press

2. SQL Server Logins and Users, http://www.akadia.com/services/sqlsrv_logins_and_users.html

3. How to transfer the logins and the passwords between instances of SQL Server 2005, http://support.microsoft.com/kb/918992

4. SP_CHANGE_USERS_LOGIN (Transact-SQL), SQL Server 2005 Books Online (November 2008), http://msdn.microsoft.com/en-us/library/ms174378(SQL.90).aspx