Search the Site

Sponsors

bottom corner

Fixing orphaned users after attaching or restoring a SQL Server database

SQL Server makes moving (attaching) or restoring a database a very simple process. However, when the process is complete, you may have users in the database that are orphaned; or in other words, they do not have the appropriate users created in the SQL Server's master database. This note provides a very simple way to repair this problem using the stored procedure sp_change_users_login.

This page is filed under keyword(s): sqlserver.

I ran into this issue when I took a backup set from a production database and restored it into a machine in my development environment. I noticed that the recently restored database has an user named "bob", but when looking at bob's entry in the newly restored database, the Login Name field is blank. To fix this, I made use of SQL Server's built-in stored procedure sp_change_users_login. The syntax is below:

Syntax:

sp_change_users_login 'action'
	[, 'user']
	[, 'login']
	[, 'password']

Valid values for 'action':

Report
Update_One
Auto_Fix

The "Report" action displays a report of all possible orphans in the current database and requires no further parameters. Sample usage:

exec sp_change_users_login 'Report'

The "Update_One" action links a specified orphaned user in the current database to an existing SQL Server login. With this action, the "user" and "login" parameters must be provided, and the "password" parameter must be NULL or not provided. Sample usage:

exec sp_change_users_login 'Update_One', 'bob', 'bob'

Finally, the method I chose to perform to fix my issue was the "Auto-Fix" action, which saves me the step of having to manually create the "bob" SQL Server login first because it will automatically create the login if it does not already exist. The "Auto_Fix" action requires the "user" and "password" parameters, and the "login" parameter must be NULL. Sample usage:

exec sp_change_users_login 'Auto_Fix', 'bob', NULL, 'password123'

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): sqlserver.
Author: C. Peter Chen
Last updated: 30 Apr 2009

bottom corner