PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete

Article translations Article translations
Article ID: 274188 - View products that this article applies to.
This article was previously published under Q274188
Expand all | Collapse all

On This Page

SYMPTOMS

When you restore a database backup to another server, you may experience a problem with orphaned users. The Troubleshooting Orphaned Users' topic in SQL Server Books Online does not outline the exact steps for troubleshooting this problem.

This article describes how you can troubleshoot orphaned users.

STATUS

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

MORE INFORMATION

Although the terms login and user are often used interchangeably, they are very different. A login is used for user authentication and a database user account is used for database access and permissions validation. Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to the SQL Server server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.

When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This condition is known as having "orphaned users."

Troubleshooting Orphaned Users

When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it.
  1. Add a login to the master database, and specify 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. Backup the database.
    BACKUP DATABASE Northwind
    TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
    						
  4. Restore the database to a different SQL Server server:
    RESTORE DATABASE Northwind
    FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
    						
    The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.
  5. Now, to detect orphaned users, run this code:
    Use Northwind go sp_change_users_login 'report'
    						
    The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the Northwind database, and the sysxlogins system table in the master database.

Steps To Resolve Orphaned Users

  1. Run the following command for the orphaned user from the preceding step:
    Use Northwind
    go
    sp_change_users_login 'update_one', 'test', 'test'
    						
    This relinks the server login "test" with the the Northwind database user "test". The sp_change_users_login stored procedure can also perform an update of all orphaned users with the "auto_fix" parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.
  2. After you run the code in the preceding step, the user can access the database. The user may then alter the password with the sp_password stored procedure:
    Use master 
    go
    sp_password NULL, 'ok', 'test'
    						
    This stored procedure cannot be used for Microsoft Windows NT security accounts. Users connecting to a SQL Server server through their Windows NT network account are authenticated by Windows NT; therefore, their passwords can only be changed in Windows NT.

    Only members of the sysadmin role can change the password for another user's login.

REFERENCES

For additional information about how to resolve permission issues when a database is moved between servers, click the article number below to view the article in the Microsoft Knowledge Base:
240872 INF: How to Resolve Permission Issues When a Database is Moved Between SQL Servers

Properties

Article ID: 274188 - Last Review: October 30, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbcodesnippet kbpending kbprb KB274188

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com