SQL Orphaned Username Fix

What is an orphan user:

Orphaned users occur when users exists in a database but is not correlated to a server login. This can occur if a restore is done from another SQL server instance where the user existed and the login does not exist on the new server or the login was dropped.

How to fix an orphaned user:

Often times after database restores in SQL 2000, 2005, and 2008 users can become orphan. The first thing you'll notice is you cannot map a user. If you can't assign a username to a SQL database it’s because the username already exists. You can run the following query in the Query Analyzer to determine whether or not this is in fact that issue that you are experiencing.

sp_change_users_login 'report'

Select all Open in new window

This will tell you if you have orphaned users and if so will returned the orphaned users.
If results are returned simply use the orphaned usernames to replace USERNAME in the below query. After you have made the change execute the below query.

sp_change_users_login @action = 'update_one', @usernamepattern = ' USERNAME ', @loginname = ' USERNAME '

Select all Open in new window

If your database has multiple users you will need to execute this for each user. This should also automatically mark the user as DBO. If you need to change this afterwards you can modify this under the users properties.

How to avoid orphaned users:

To avoid orphaned users when restoring from a .bak file you should ensure that the same user(s) exist on the server you are restoring to before running the restore. The best method of restoring is to create an empty duplicate database with the same name and users and file locations to avoid complications when restoring to another instance SQL server.

Remember:

Always backup your database before making any changes!