Tech Tip: Fixing Microsoft SQL Server Orphan Users

What are orphan users?

In Microsoft SQL server, there are two types of users:

• Login aka Server-level users
• Users aka Database-level users

Logins are only concerned with access the SQL Server instance. You can access the SQL server instance via the login's credentials using an SQL Server client like SQL Server Management Studio or Azure Data Studio.

However, with only login, you can access the SQL Server instance but you will not have access to the databases in the SQL Server instance.

For the login to access a specific database, the login must be mapped to a database user. A database user does not have its own credentials and relies on the credentials of the login.

What causes orphan users?

When you backup an SQL Server database and restore the database into a different SQL Server instance. The mapping between the database user and the login is lost.

This is because the only the database user is backed up and restored. The login information of the previous SQL Server instance is not included as part of the back up. Hence, that database user is orphaned as it has no logins associated with it.

How do I identify and and fix orphan users?

To identify orphan users, you can run the following SQL command in the database:

EXEC SP_CHANGE_USERS_LOGIN 'REPORT'

To fix orphan users, you may use any of the following approach

The manual approach

You can delete the orphan user in the database and create a new login and database user for the database. However, this would mean that you will lose all the database permissions assigned to that orphaned database user.

Fix orphan users by creating a new login using SQL Command

To fix orphan users by creating a new login, use the following command:

sp_change_users_login AUTO_FIX, <username>, NULL, <password>

where <username> and <password> is the username and password of the new SQL login.

An example of the above is shown below:

EXEC sp_change_users_login 'NewUser', NULL, 'TestPassword';

Fix Orphan Users by Mapping to an Existing Login with the Same Name

To fix orphan database users by mapping it to an existing login with the same name, use the command below:

EXEC sp_change_users_login 'Auto_Fix', <username>

An example of the above is shown below:

EXEC sp_change_users_login 'Auto_Fix', 'LoginName'

Fix Orphan Users by Mapping to a Login With a Different Name

To fix orphan database users by mapping it to to a login with a different name, you can use the following command:

ALTER USER <orphan database user name> WITH LOGIN =  <login name>

An example is:

ALTER USER MyOrphanDatabaseUserName WITH LOGIN = MyLoginName

If you are using AWS RDS, it is preferable to use the above SQL command as sp_change_users_login is not accessible.