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+