Blog Archives

Fixing Orphaned Users In SQL 2008 R2

Here is one that happens to all of us once in a while, and we may tend to forget how to resolve it. I had a database copied from one server to another. I really did not want to have to go through and reset permissions on all the stored procedures, so I needed to save that orphaned user that was listed in the new database’s User list.

The fix is really easy. First I created the user on the server. I did not select any user mappings and left the roles public. To keep things simple I named it the same as the user name in the database; for this example I will call it MySQLUser. Next I ran the magic SQL command.

sp_change_users_login @Action='update_one', @UserNamePattern='MySQLUser', @LoginName='MySQLUser';

If you want more detail about troubleshooting orphaned users you should go to this MSDN page.