9/27/2011

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 )

Reactions:

2 comments:

  1. What if I get this error and it's a brand new database, NOT a restore?

    How do I fix this?

    Why does this happen?

    Thanks.

    ReplyDelete
  2. Hi,

    The reason for Orphaned user is mismatch of Login SID.
    Here are the steps to reproduce the error:
    ==========================================
    1) Create a TestDB,
    2) create a login and map it to the testdb
    3) Captured output for select * from sys.syslogins.
    4) Then drop the login created in step 2
    5) recreate the login (the same name) this time do not map it to testdb.
    6) And, now, query and Captured output for select * from sys.syslogins
    7) compare the output, you will notice that SID for the logins are different

    * I assume this could have happened in your case.

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri