Orphaned SQL Server Users

It’s a problem almost every MS SQL Server DBA has encountered at least once. You restore a DB that was on one machine to another machine. All the users exist in the DB, but you can not log in as those users (even if they exist on the new machine). Maybe you’ve deleted all the users from the DB then added them back, or maybe you’ve used the following functionality.

First of all, to be certain that orphaned users are the problem run the following command:
EXEC sp_change_users_login ‘Report’
Next, if you already have a LoginID and Password at the SQL Server level, you can fix it by running the following command:
EXEC sp_change_users_login ‘Auto_Fix’, ‘UserId
If you need to create a new LoginID and Password for this user, you can run the following command:
EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘UserID‘, ‘Password

Essentially what has happened to your users is a simple issue. Behind the scenes in SQL Server each user has a hidden GUID. This GUID is not going ot be the same across servers, so the GUID in the DB is looking at the wrong (or missing) GUID at the server level.


About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s