Restoring Orphaned SQL Server 2008 Users
When moving databases across servers and reinstalling SQL Server almost everything transitions perfectly except SQL Server Users. Unless the user already exists in the Master Security table, the database user is left orphaned inside the database without the ability to perform any useful function. Like you right now, but we're gonna change that.
Here is an example, note how there is a User Name but no Login Name.
User Name but no Login Name
Step 1: Recreate the User in Master
First, if they don't already exist, is to recreate the user in the master database by browsing to the root level Security folder in SQL Server Management Studio, choose Logins and right-click create user.
You tried to check the database under the "User Mapping" page didn't you?
Create failed for User 'UserName'.
That was a good idea hotshot, but it ain't gonna work for you. Just create the user and Ok your way out of there. Time for Step 2.
Step 2: Remap User to Database
We need to run a system stored procedure I found on MSDN, it is pretty straightforward, unfortunately it must be performed for each database.
NOTE: There's got to be a better way, and supposedly this method is deprecated, but this is the only thing I could get to work in my instance, so if you got something better please share.
USE DATABASENAME; GO EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'UserPassword'; GO
DATABASENAME = The name of your database
UserName = SQL Server Login to repair
UserPassword = SQL Server Login's password to repair
Full documentation on MSDN for sp_change_users_login
What is this?
We use the IFWIS Blog to keep our staff, cooperators and the public updated on what we are working on, what's new and share tips about using our software and website.
The content of the IFWIS Blog is often highly technical, but we try our best to keep our posts accessible and jargon-free. When this is not possible, we'll link to other resources online to provide background. Thanks for reading and please follow our blog for updates.
- IFWIS RSS Feed
- Daily Email Digest (All Public Content)
- Or follow just one of the many topic-specific tag feeds or pages below.