There is a point to this story, but it has temporarily escaped my mind...
Contact Me MyFaceBook MyLinkedIn MyGitHub MyTwitter

How to Fix Orphaned Users

I sometimes move databases between production, test, and dev. Sometimes I backup and restore, sometimes I detach/attach the databases. Almost everytime I do this where I'm using SQL logins, I find orphaned users in the database even though I've created the user in SQL Server prior to the restore/attach.

All users are linked via a SID to a login and if you have SQL Server logins, who’s SIDs are managed by SQL Server. If the database is using Windows Logins, this does not occur because the SID is controlled by Windows or Active Directory. So, how do you fix the problem without dropping and re-creating the user? Use a stored procedure called sp_change_users_login to fix orphaned users. This stored procedure can do several things but the two things I use it for is to tell me which users are orphaned, and let me fix the user.

To find orphaned users:

USE [ESB_UR43]
GO

EXEC sp_change_users_login 'REPORT'

UserName  UserSID
--------  -----------------------------------
uni_sa    0xB0FC618806E338429B80171B88A42F00

To fix this orphaned user, I run sp_change_users_login with the UPDATE_ONE action and provide the name of the orphaned user and the name of the server user login.

USE [ESB_UR43]
GO

EXEC sp_change_users_login 'UPDATE_ONE','uni_sa','uni_sa'

Once this is completed, you should be able to manage the user mappings and permissions using the SQL Manager Tools.

Copyright © 2015 by Julian Easterling. SOME RIGHTS RESERVED.

Creative Commons License
Except where otherwise noted, content on this site is
licensed under a Creative Common Attribution-Share Alike 4.0 International License.


All of the opinions expressed on this website are those of Julian Easterling and
do not represent the views of any of my current and previous clients or employers in any way.

If you notice an error on the site or content that has not been properly attributed, bring
it to my attention using the contact page and I will endeavor to fix it as soon as I can.

I accept no responsibility or liability for any damages incurred by following any of
my advice or by using any of the information on my site or of those sites that I link to.