SQL Orphaned user Solution

When you restore a database backup to another server, you may encounter problems with orphaned users. The specific steps to resolve this issue are not covered in the Troubleshooting Orphaned users topic in SQL Server Books Online.

This article describes how to troubleshoot an orphaned user problem.

State

Microsoft has confirmed that this is a problem in the Microsoft products listed at the beginning of this article.

More information

Although the term "login" and "user" are often exchanged, there is a big difference between them. Logins are used for user authentication, and database user accounts are used for database access and permission validation. Logins are associated with the user through a security identifier (SID). Accessing the SQL Server server requires logging on. The process of verifying that a specific login is valid is known as "authentication." The login must be associated with a SQL Server database user. You use user accounts to control which activities are performed in the database. If a user account for a particular login is not present in the database, the user who uses the login cannot access the database even if they are able to connect to the SQL Server server. However, the only exception to this scenario is when the database contains a "guest" user account. Logins that are not associated with a user account are mapped to the guest user. Conversely, if a database user exists but there is no login associated with it, the user will not be able to log on to the SQL Server server.

When you restore a database to another server, the database contains a set of users and permissions, but there may not be a corresponding login or the user associated with the login may not be the same user. This situation is known as "orphaned users."

Troubleshoot orphaned users

When you restore a database backup to another server, you may encounter problems with orphaned users. The following situation illustrates the problem and explains how to address it. 1. Add a login to the primary database and designate the default database as Northwind:use master go sp_addlogin ' test ', ' Password ', ' Northwind '

2. Grant access to the user you just created: Use Northwind go sp_grantdbaccess ' test '

3. Back up the database.

BACKUP DATABASE Northwind

to DISK = ' C:\MSSQL\BACKUP\Northwind.bak '

4. Restore the database to another SQL Server server:

RESTORE DATABASE Northwind

From DISK = ' C:\MSSQL\BACKUP\Northwind.bak '

The restored database contains a user named "test" but does not have a corresponding login, which causes "test" to become an orphaned user.

5. Now, to detect orphaned users, run this code: Use the Northwind go sp_change_users_login ' a '

The output lists all logins, which contain mismatched entries in the sysusers system tables of the Northwind database and the sysxlogins system tables of the primary database.

Steps to resolve orphaned user issues

1. Run the following command for the orphaned user in the previous step:

Use Northwind

Go

Sp_change_users_login ' Update_One ', ' Test ', ' test '

This will reconnect the server login "test" with the Northwind database user "test". Sp_change_users_login stored procedures can also use the "Auto_Fix" parameter to perform updates on all orphaned users, but this is not recommended because SQL Server attempts to match logins and users by name. This is possible in most cases, but if the user is associated with an error login, the user may have incorrect permissions.

2. After running the code in the previous step, the user can access the database. The user can then change the password using the sp_password stored procedure:

Use master

Go

Sp_password NULL, ' OK ', ' test '

This stored procedure cannot be used with a Microsoft Windows NT security account. Users who connect to a SQL Server server through a Windows NT network account are authorized by Windows NT, so they can only change their passwords in Windows NT.

Only members of the sysadmin role can change the logon password of another user.

Reference

For additional information about how to troubleshoot permission issues when you move a database between servers, click the article number below to view the article in the Microsoft Knowledge Base:

240872 INF: How to resolve permissions issues when moving a database between SQL servers