Watch for orphaned users after restoring a database

Get a quick way to find out if user accounts are orphaned -- or fail to match the information in SQL Server's master database -- following a database restore.

You'll find "orphaned" users in SQL Server when a user's account information in a database does not match the account information stored in SQL Server's master database. Even if the user account name is the same, the user account's SID (security identifier) may not be. An account with the same name but a different SID is not the same account.

This issue typically crops up when transferring a database from one server to another, but it can happen if an older version of a database is restored --one that relies on user accounts that don't exist anymore or weren't recreated to match if the database server was rebuilt at some point.

The quick way to find out if user accounts are orphaned is to open Query Analyzer and type:

USE <database>
EXEC SP_CHANGE_USERS_LOGIN 'Report'

This produces a report with the names and SIDs of orphaned users; is of course the name of the database in question.

If you see that the database owner (dbo) is listed as orphaned, run this command to repair this. (You can run the same stored procedure again to change the owner to another user if you wish.)

USE <database>
EXEC SP_CHANGEDBOWNER 'sa'

To repair the actual orphaned users, you can use several approaches:

1. Drop all of the existing users in the database, recreate the user names (with new SIDs to match) and add those users to the database again. The downside to this approach is that if you have a lot of objects to grant permissions to, such as stored procedures, this will be extremely slow. If you have scripts to do this, it can make the job a great deal easier.

2. Use a pair of Microsoft-supplied stored procedures, sp_sidmap and sp_prefix_sysusersname, to repair the connections between usernames and SIDs. A package containing these stored procedures and an set of instructions for their use can be downloaded from Microsoft Knowledge Base article 298897. Detailed instructions for its use can be found in article 240872. Be sure to back up the master database before attempting this.

As a general rule of thumb, any process involving making changes to user permission should be done when the database is in single-user mode.

About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!


More information from SearchSQLServer.com

  • Tip: How to restore from a transaction log
  • Ask the Experts: Restoring a database from another SQL Server
  • Topic: Get more backup and restore best practices in this topics section


  • You'll find "orphaned" users in SQL Server when a user's account information in a database does not match the account information stored in SQL Server's master database. Even if the user account name is the same, the user account's SID (security identifier) may not be. An account with the same name but a different SID is not the same account.

    This issue typically crops up when transferring a database from one server to another, but it can happen if an older version of a database is restored --one that relies on user accounts that don't exist anymore or weren't recreated to match if the database server was rebuilt at some point.

    The quick way to find out if user accounts are orphaned is to open Query Analyzer and type:

    USE <database>
    EXEC SP_CHANGE_USERS_LOGIN 'Report'

    This produces a report with the names and SIDs of orphaned users; is of course the name of the database in question.

    If you see that the database owner (dbo) is listed as orphaned, run this command to repair this. (You can run the same stored procedure again to change the owner to another user if you wish.)

    USE <database>
    EXEC SP_CHANGEDBOWNER 'sa'

    To repair the actual orphaned users, you can use several approaches:

    1. Drop all of the existing users in the database, recreate the user names (with new SIDs to match) and add those users to the database again. The downside to this approach is that if you have a lot of objects to grant permissions to, such as stored procedures, this will be extremely slow. If you have scripts to do this, it can make the job a great deal easier.

    2. Use a pair of Microsoft-supplied stored procedures, sp_sidmap and sp_prefix_sysusersname, to repair the connections between usernames and SIDs. A package containing these stored procedures and an set of instructions for their use can be downloaded from Microsoft Knowledge Base article 298897. Detailed instructions for its use can be found in article 240872. Be sure to back up the master database before attempting this.

    As a general rule of thumb, any process involving making changes to user permission should be done when the database is in single-user mode.

    About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!


    More information from SearchSQLServer.com

  • Tip: How to restore from a transaction log
  • Ask the Experts: Restoring a database from another SQL Server
  • Topic: Get more backup and restore best practices in this topics section

  • This was first published in January 2006

    Dig deeper on SQL Server Backup and Recovery

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close