Microsoft SQL Server Orphaned User

You have been logging in to SQL Server with a user name only to get Microsoft SQL Server, Error: 18456 one day. You verified that it is a valid user in the database you want to access.

Orphaned User is a term use to describe a condition where a database user does not have a properly defined SQL Server login. As discussed in SQL Server Login and Database User, the mapping of SQL Server login to a Database User is stored within the user database. The mapping information includes the name and the SID of the corresponding SQL Server login.

Orphaned user appears when a database user does not have a SQL Server login of the same name, or having SQL Server login with the same name but different SID. The most common cause is restoring or attaching a database to a different instance of SQL Server. User can also become orphaned when the corresponding SQL Server login is deleted (dropped in SQL Server terminology).

How to Identify

Basically you shall suspect a possibility of orphaned user when you cannot login to the database with the login you usually do or one that you are sure is valid. You may verify by looking at the database user property. The following figures show the property of proper database user and orphaned database user. Notice that the orphaned user is without a login.

There is a more technical way to detect orphaned user. You may run the procedure sp_change_users_login with the Action parameter Report on the database. You may execute it in Microsoft SQL Server Management Studio or any database client software. The script will return a list of orphaned user name and SID for the particular database. The following is the T-SQL script to detect orphaned user and the subsequent figure shows the result. The result pane would be blank if there is no orphaned user.

USE database_name
EXEC sp_change_users_login @Action='Report'

Remember that user is at database level. You will get the list of orphaned user for the database you execute the previous script on only. That is why I have included the script to change database prior to executing the procedure sp_change_users_login.

How to Resolve

Orphaned user could be resolved fairly easily. You have to first find the login to be mapped to the orphaned user. You will create a new login if there is not one appropriate. Then map the intended login with the orphaned user.

1. Find or create Appropriate Login

The user name that you supplied to access the database server is a login. You should find a login similar to your user name. If you cannot find one, create a new login with the following script.

For windows login, CREATE LOGIN [Domain\User] FROM WINDOWS

For SQL Server login, CREATE LOGIN login_name WITH PASSWORD='password'

2. Map the Orphaned User to Selected Login

The following T-SQL script will do the job.

USE database_name
EXEC sp_change_users_login @Action='update_one', @UserNamePattern='orphaned user', @LoginName='selected login'

Final Thoughts

If you are not familiar with T-SQL scripts and find uncomfortable with the resolution above, there is a more “windows” way. However, this is provided your system does not enforce access rights at database level like Microsoft Dynamics Ax.

Such system will configure the user to hold certain fixed database role. Dynamics Ax for example, the user will have db_datareader, db_datawriter and db_ddladmin fixed database roles. You could find this on the Database User property page.

In order to resolve this using SQL Server Management Studio, you will have to first identify the database roles mentioned above. Then, drop the orphaned user. Finally, map the Login to the database from SQL Server Login property page under the User Mapping tab. You will set the database role on the same page.