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:
