How to prevent executing delete or update SQL statement without a WHERE clause

Question

Hi,

I am seeking a solution to prevent executing the delete or update SQL statement without a WHERE clause in the SQL Server database level.

May TRIGGER is an option, but it works on individual table levels, I am seeking a solution to prevent it for all tables in a database. Is it possible to do it by using Policy-Based Management in SQL Server?

Thanks in advance.

Answer 1

Hi @Mr. Uzzal Kumar Hore

You could create a trigger on single table.

Refer to this blog: Can you prevent deletes and inserts without a WHERE clause from running?

Is it possible to do it by using Policy-Based Management in SQL Server?

I would say No. Policy-Based Management provide a way to define the standards that control the SQL Server and database objects configuration. These configurations can be enforced on the database objects, database, SQL instance or multiple instances levels. It sems not possible to monitor SQL statement.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

there is no support for restricting sql syntax, only table and column access.

the closest you can come is a table trigger that checks if deleted row count matches the table row count (meaning all rows were updated or deleted). the trigger can do a rollback (this may be a long running rollback). in some isolation levels, in a highly active table, it may fail to detect all cases.

so for example:

update table mytable set mycolumn = @v where 1 = 1

may be rolled back.