Orphaned Sql Server users

Posted by Matheus Guimaraes |11 Jan 16 |

Uncategorized

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)