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:
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.
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'
GOThe result below shows the SID of the SQL Server login and that of the user database id differ. This is what causes the problem.
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
Link User and Login to Fix Orphan User in SQL Server
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'
GOOr 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'
GOBoth 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'
GOSummarizing 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
- 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.
- See these related articles:
