July 2, 2010 at 11:55 am
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.
July 2, 2010 at 12:01 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply