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.

Advertisements

About SheldonS

Web developer for over 15 years mainly with Microsoft technologies from classic ASP to .NET 4. Husband, father, and aspiring amateur photographer.

Posted on March 8, 2012, in SQL and tagged , . Bookmark the permalink. Leave a comment.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: