Accidental deletes at SQL Server Subscriber

Question

Unfortunately, this happened twice in 2 weeks on our most critical db server; just before Christmas! Instead of running a delete script at the publisher, the script was run at the subscriber, which broke replication!

I'm exploring options to avoid this scenario in future. This is a massive DataWarehouse. Lot of transactions. Everyone and his brother in the organization has access. Any more such mistakes might trigger a resume-generating-event.

Here are some of my notes:

1. Maybe I can use the deny_db_writer role at the subscriber. But I believe sysadmins will still be able to run the deletes.

2. INSTEAD OF DELETE TRIGGERS could be an option; however, we have hundreds of tables, that needs these triggers. I don't know if the genuine deletes from replication will invoke this trigger or not. (we prefer NOT to. There's something called NOT FOR REPLICATION for triggers; we might have to use that)

3. Can this be achieved by a DDL trigger? what could be the impact on the subscriber?

Has anyone done anything like this? Thank you so much for any suggestions.

Answer 1

Maybe I can use the deny_db_writer role at the subscriber. But I believe sysadmins will still be able to run the deletes.

Yes. But they are sysadmins. And so are responsible for their own "resume-generating-events". If there are sysadmins who aren't trusted DBAs who should know better, then you'll need to reduce their permissions.

The right answer here is to prevent the data modification at the subscriber through security. You can run

deny delete to public

This will override any permissions and prevent deletes database-wide for all users (public is the 'everyone' role in a SQL Server Database).

Or you can limit this to a single schema:

deny delete on schema::dbo to public

Which would allow users to delete from tables located in schemas they own.

With DELETE denied to PUBLIC, only dbo, or a sysadmin (who always connect as dbo), or a stored procedure owned by dbo will be able to delete.