Orphaned Users
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“.
Possible Reasons for Orphaned users:
- The most common reason for ending up with orphan users is, moving databases from one server to another or from one domain to another.
- Restoring an old copy of master database can also result in this problem.
All SQL Server logins are stored in the system table sysxlogins (in master database). Whenever you create a new login (or grant access to a Windows account), a corresponding entry gets added to the sysxlogins table. So, you could say, each login has a corresponding row in sysxlogins table. In sysxlogins, each login has an associated SID (Security Identifier), which is of type varbinary (85).
Every database contains a table called sysusers, which stores a list of all users who have access to that database. That is, sysusers actually contains a list of logins that have been granted access to this database. So, whenever you grant access to a login into a particular database, an entry gets added into the sysusers table of that database. This entry refers to the login in sysxlogins, using the SID column. Based on this SID linking, SQL Server can map all database users to their corresponding logins as given below:
When you move (using backup/restore or detach/attach) a database to a new server. Since sysusers table is stored within the database, it gets moved over to the new server as well. Now the new server might or might not have the same logins, and the SIDs of these logins could be completely different from the SIDs of these logins in the original server. What this means is that, the sysusers table in the newly moved database has SIDs that aren’t anymore there in the sysxlogins table on this new server. So, SQL Server cannot map the users in this database to any of the logins. That’s what results in orphaned users.
Most common Errors:
- Applications will experience ‘login failed‘ error messages and fail to log into the database.
- Users won’t show up in Enterprise Manager, but when you try to add users, you will get error messages saying ‘User or role already exists in the current database‘ .
Troubleshooting Orphaned Users:
To detect orphaned users, run the below code:
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 specified database and the sysxlogins system table in the master database.
Steps to resolve the issue:
The below code relinks the server login with the the specified database user . 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.
use <database_name>
gosp_change_users_login ‘update_one’, ‘<login name>’, ‘<user name>’
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, ‘<new password>’, ‘<login>’
