Tip

Watch for orphaned users after restoring a database

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

    Requires Free Membership to View

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

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    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:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.