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 )
What if I get this error and it's a brand new database, NOT a restore?
ReplyDeleteHow do I fix this?
Why does this happen?
Thanks.
Hi,
ReplyDeleteThe 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.