I have set up Transaction Replication and its working well.
I have a few different web apps that connect to the publisher and subscriber database and I am concerned that if they use the subscriber database for inserting/updating/deleting intentionally, or accidentally data will be lost. (it should essentially be used as a read-only database because I have not setup updatable subscriptions).
I tried changing the database properties to be read-only, but this caused the replication to fail.
I was thinking maybe a before and after script during replication to add/remove the database read-only flag?
Is there a better way to do this?
Best Answer
Instead of altering the database to become read only, create a new login and add it to the db_datareader role of the subscriber database.