Fixing orphaned users after attaching or restoring a SQL Server database

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'

Leave a Reply

Your email address will not be published.