Orphaned Sql Server users

Sometimes when restoring SQL Server databases, particularly from one machine to another, you may end up with orphaned users. These are users who don’t have any log ins associated with them. This happens because usernames are stored internally against unique ids called SIDs which are generated much like GUIDs. If those ids don’t match then SQL Server can’t find the data associated with them, even if the usernames are the same.

In order to determine if you have any orphaned users, you can run this command against any given database:

EXEC sp_change_users_login 'Report'

Any users that come up are orphaned.

The good news is that you can execute a command to auto-fix them. The bad news is that this doesn’t always work.

The command to auto-fix them is :

EXEC sp_change_users_login 'Auto_Fix', 'user'

Just replaced user with the name of the user you want to fix.

This should work if the user already exists in the system and the user names match but the SIDs don’t; however, if the user needs to be created then you will need to issue the full command and give it a password like this:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Just replace user with the name of the user that you need to fix such as those returned from the first query above, login with the username for the user and password with any given password (note that it may have to conform to your windows local password security policy)


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