What are orphaned users in SQL Server

Sometimes after restoring a database it is possible that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well. These users are called orphan users.

When you restore a Microsoft SQL Server database on a different machine, you cannot access the database until you fix the permissions.

Consider that all instructions below should be performed as a database admin, with the restored database selected.

How to find orphaned users in SQL Server?

You need to run the following query over your database:

EXEC sp_change_users_login 'REPORT'

This example produces a report of the orphaned users in the current database and their security identifiers.

How to fix orphaned users in SQL Server?

There are several ways how to fix orphaned users in SQL Server:

1. Way - Delete the user from database:

Security > Users > Right click on the user > Delete

Remap user from logins:

Security > Logins > right click on the login > Properties > User Mapping > Select (checked) database in Users mapped to this login part > choose desired roles for the user in Database role membership > Click OK

This process actually removes restored user and map existing logins to a database.

2. Way - Map an existing user in a database to a Microsoft SQL Server login using code:

EXEC sp_change_users_login 'Update_One', 'DB_Username', 'Login_Username'

3. Way - If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

4. Way - 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'

How to fix orphaned dbo user in SQL Server?

When you try to change fix the issue with sp_change_users_login you could get an error message saying that dbo is a forbidden value for the login name parameter in this procedure. So, if the database owner (dbo) is listed as orphaned, run this code in the user database to fix an orphaned dbo user in SQL Server:

EXEC sp_changedbowner 'sa'