How to prevent table deletion for all users

Question

In SQL Server 2005, is there a way, with a single statement, to prevent row-wise deletion in a specific table for all database users?

Answer 1

try the following:

CREATE TRIGGER yourTriggerName ON YourTableName INSTEAD OF DELETE AS ROLLBACK RAISERROR('ERROR, DELETEs not permitted in YourTableName!!!',16,1) RETURN go 

working sample:

 CREATE TABLE XYZ (RowID int) INSERT XYZ VALUES(1) INSERT XYZ VALUES(2) go CREATE TRIGGER yourTriggerName ON XYZ INSTEAD OF DELETE AS ROLLBACK RAISERROR('ERROR, DELETEs not permitted in XYZ!!!',16,1) RETURN go delete XYZ 

CONCLUSION:

 Msg 50000, Level 16, State 1, Procedure yourTriggerName, Line 6 ERROR, DELETEs not permitted in XYZ!!! Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted. 

Answer 2

It is an extreme view, but you can watch INSTEAD OF DELETE to ignore deletions. You may cause an error like "delete access ... denied"

Obviously, users with db_owner privileges can disable the trigger.

Answer 3

if you want to limit the row to the base, create a table (to lock) and insert the identifiers of the rows you want to protect. and create a relationship.

Answer 4

Triggers only (or refactoring your security / permission model)

In one example, where permissions do not help: if you saved procs that delete rows, and both proc / table have the same owner, permissions for not tables, even DENY, will be allowed. See ownership chain . Thus, users can delete or modify data without any rights in the table.