Blog Post

workaround for orphaned users

The server principal  is not able to access the database  under the current security context, is the error message appears for those users who was able to access the database / application earlier especially after refreshing database from production to dev, test or uat enviornment when the correspondent login is dropped.
The reason is that, when we restore the database the the SID for the user mismatches, and it became orphaned. The workaround for this issue is very simple, you will have execute the below code

 -- This part will report if there are orphaned users    
USE 'yourdbname'
GO
sp_change_users_login
    @Action='Report'
GO
-- This part will fix orphaned users
USE 'yourdbname'
GO
sp_change_users_login
    @Action='update_one'
    ,@UserNamePattern='youruser'
    ,@LoginName='yourlogin'
GO

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating