Microsoft SQL Server Tips Series: Orphaned Users

There is a problem you might encounter when restoring a database which has database users attached to it.  They will lose their mapping to their respective server login, and this will prevent you from logging into the database.  In this short article, I’ll show you how the problem is created, and how you can fix it.

Creating the Problem

As usual, I’ll start by attaching a copy of the AdventureWorks database to my local SQL Server instance.  Next, I’ll create a server login mapped to a database user called dbtestuser.

Now, make a backup copy of this database, upload it to your hosting account, and perform a restore operation using the DiscountASP.NET Control Panel.  If you log into your DiscountASP.NET database using SQL Server Management Studio and expand both Security Folders, you’ll notice the database user was restored but not the server login.

Normally, this isn’t an issue since you can just delete the database user and re-create the server login and database user.  We even provide this Knowledge Base article to assist you; however, one customer had this dilemma where his database users had permissions already assigned to objects in his database, and he wanted to keep them.  Recreating the logins was not an option in this case.

We will go ahead and create a server login with same name as the database user (in this case, dbtestuser) through the DiscountASP.NET Control Panel.

But when you right click on the server login dbtestuser, select Properties, and then User Mapping in SQL Server Management Studio, you’ll notice it’s not mapped to a database user.

If you check the box and try to map it to the database user through the SQL Server Management Studio GUI, you will get an error.

Unfortunately, the mapping cannot be restored using the GUI tools.

The Solution

The solution is very simple.  After you have created the server login through the DiscountASP.NET Control Panel, you can just open a New Query window and execute the system stored procedure sp_change_users_login or use the ALTER USER command to restore the mapping.  I’ve included sample syntax below:

sp_change_users_login N’Update_One’, N’dbtestuser’, N’dbtestuser’

 

ALTER USER dbtestuser
WITH DEFAULT_SCHEMA = dbo,
LOGIN = dbtestuser

Now, when you right click on the server login dbtestuser, select Properties, and then User Mapping, it will show that it’s mapped correctly, and you will be able to log into your database.