Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.

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 )

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...