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;
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
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;
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
This was first published in January 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation