patternsqlMinor
SQL Log Shipping Read Only/Standby Different users
Viewed 0 times
readsqllogstandbydifferentshippingusersonly
Problem
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+
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+
Solution
This can easily be achieved if you disable the login on the primary:
- Add the login on the primary
- Add the user to the database and grant permissions
- Copy the login to the secondary
- Disable the login on the primary
Context
StackExchange Database Administrators Q#214303, answer score: 4
Revisions (0)
No revisions yet.