In our scenario we have created transactional replica of our the production database to the staging machine where developers can prepare queries to be later executed in production. But in few days one of developers executed prepared DELETE statement directly against replica and the replication stopped to work with the error: The row was not found at the Subscriber when applying the replicated command. To prevent this situation we have decided to disable all DML operations against this replica for all accounts except of the system one used by replication agent. After searching for various solutions the easiest way (not saying that the best one) for us was to generate triggers where all transactions from other then replication accounts will be rolled back.
Below is simple script to generate required triggers for every table in database. You only need to set @ReplAccount variable to your replication system account. If you will later remove all triggers you can switch @DeleteOnly variable from 0 to 1.
DECLARE @Stmt NVARCHAR(MAX) DECLARE @DeleteOnly BIT DECLARE @ReplAccount NVARCHAR(128) SET @ReplAccount = 'domain\account' SET @DeleteOnly = 0 SET @Stmt = '' SELECT @Stmt = @Stmt + CASE @DeleteOnly WHEN 0 THEN N'EXECUTE(''CREATE TRIGGER ['+ [sch].[name] + '].[trg_prevent_modify_' + [tab].[name] + '] ON [' + [sch].[name] + '].[' + [tab].[name] + '] FOR INSERT, UPDATE, DELETE AS BEGIN IF SUSER_SNAME() <> ''''' + @ReplAccount + ''''' BEGIN RAISERROR(''''Database is replicated and data cannot be modified!'''',16,1); ROLLBACK; END END''); ' ELSE 'DROP TRIGGER ['+[sch].[name]+'].[trg_prevent_modify_'+[tab].[name]+']' + CHAR(13) END FROM sys.tables AS tab JOIN sys.schemas AS sch ON tab.[schema_id] = sch.[schema_id] WHERE tab.[type] = 'U' PRINT @Stmt EXECUTE(@Stmt) GO
If you will test this solution the try to update the data in any table and the following exception will be generated: