How to deny delete on a table for all users

Within SQL Server 2005, is there a way, with a single 开发者_JS百科statement, to deny delete on rows in a particular table for all users of the database?

try this:

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

OUTPUT:

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.

Kind of extreme, but you can look at an INSTEAD OF DELETE trigger to ignore the deletions. You could raise an error similar to 'delete access ... denied'

Obviously users with db_owner access privileges could drop the trigger

if you want to row based restriction create a table (for lock) and insert ids of rows that you want to protect. and create a relation.

Only triggers (or refactor your security/permission model)

One example where permissions won't help: If you have stored procs that delete rows, and both the proc/table have the same owner, table permissions will not be checked, even DENY. See ownership chaining. So users can delete or change data without any rights on the table at all.