How to solve the problem orphan users in SQL Server databases?

First you need to know what is orphaned user?
SQL Server maintains user logins in the sysxlogins system table in the master database. Every time you add a user, or give a Windows account access, an entry is made in this table. Every user is assigned a security identifier (SID) in sysxusers. Each database has a table called sysusers that contains a list of users that have access to that database. Typically, the SID in the sysusers table matched the SID in sysxusers.
When a user¬†entry exists in sysusers that that does not have a matching SID in sysxusers, that user is “orphaned.

Cause : The user in the database is an “orphan“. That means there is no login id or password associated with the user. When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions.
Or
If you are using the Plesk Control Panel and creating the Database and if you restore the database, in the same moment you will be getting error as warning Warning: Database was restored successfully, but some database users require your attention. There are several orphaned users that should be repaired to function properly. View users now.

If you click on the repair then you will be asking for user id and password and then you can able to connect to the database.

Or
Orphaned users occur when moving a database from one server to another. The new server either does not have the existing users, or the users’ SIDs do not match the ones from the old server. Orphaned users can also occur when restoring an older version of the master database.

Solution : SQL Server provides the sp_change_users_login stored procedure to help fix orphaned users.
To run the stored procedure, open SQL Server Management Studio (Express version works too) and create a new query. Set the database to the one with the orphaned users and execute the following once for each user that needs to be fixed. Make sure to replace ‘USER’ with the actual user id

exec sp_change_users_login ‘auto_fix’, ‘USER’

If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ‘password’

About the Author

avatar