3

I have a primary OLTP server on Server A and Log Shipping in Standby/Readonly mode on Server B.

Log Shipping Server is refreshed every 4 hours, and utilized for BI Reporting Analysis, to reduce load from OLTP. I want a different set of users on LogShippingDb, which Cannot access Primary OLTP. Everytime, I try to add user on LogShipping Db, it says "Failed to Update database because the database is read-only."

How do I resolve this issue, and have a different set of users on Log Shipping?

Can I also try orphaned users on Primary server? example Winad BI UserGroup on OLTP, without a login, but user, and then have both Login and User on Destination server?

Orphaned users may not be best way either, may cause issue down the line, trying to refrain from view synonym db also, we have lot of tables 1000+

  • you answered it yourself, orphaned users is the way to go. although Tara's answer on disabling login (instead of removing login) might be cleaner but does leave possibility of login being enabled inadvertently. – Bob Klimes Aug 7 '18 at 21:09
4

This can easily be achieved if you disable the login on the primary:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy