Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Standby Database inaccessible by user Expand / Collapse
Author
Message
Posted Friday, July 2, 2010 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 3, 2011 3:02 PM
Points: 5, Visits: 145
I am using SQL Server 2008 on both Servers and logshipping to the secondary database. I can't of course synch up users on the standby/read only database. I was informed by a Senior DBA to execute sp_change_users_login on the primary database and that the executed command would be captured in the T-log replayed on the secondary. I executed sp_change_users_login with both 'AUTO_FIX' and 'UPDATE_ONE' options in-between separate T-log backups and restores to the secondary and neither allowed me to successfully use the USER to access the database. Any suggestions are appreciated, Thank you.
Post #947034
Posted Friday, July 2, 2010 12:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 1,265, Visits: 3,597
This happens because the login specified has a different sid on the primary and secondary servers. The logins are mapped to database users by sid. When you restore a log backup on the secondary server, you reset the sid to the primary login.

You must create a procedure, in the master database, to script a login. This script will contain the login, including the sid the primary uses. Then run the login creation script on the secondary server. Now each login shares the same sid. This sid is assigned to the same user in each database.

Everything you need is in this link
http://support.microsoft.com/kb/918992


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #947035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse