Deny Delete on a Table to All Users Except a Few – SQL Server

I was speaking to a DBA friend of mine the other day on how do they Deny deletes on a table to all users, including admins. One of the solutions was to use a trigger as shown below:

SAMPLE TABLE

CREATE TABLE SitesTable (
ID int,
Name nvarchar(20)
)

INSERT SitesTable VALUES(1, 'SqlServerCurry.com')
INSERT SitesTable VALUES(2, 'DevCurry.com')
INSERT SitesTable VALUES(3, 'DotNetCurry.com')
GO

TRIGGER

-- Trigger by SqlServerCurry.com
CREATE TRIGGER noSitesDelete ON SitesTable
FOR DELETE
AS
IF USER_NAME() <> 'Sam'
ROLLBACK
RAISERROR('You cannot delete SitesTable!',16,1)
RETURN
GO

Now just fire the DELETE Query

DELETE FROM SitesTable
WHERE ID = 2

and you will get the following output

As you can see, only Sam can delete rows from the table. Everyone else gets the error.

Note: Remember that the trigger will not be fired if a user truncates the table, instead of deleting rows. Moreover an admin can always delete the trigger itself. In case of Admins, the trigger shown above only prevents them from ‘accidentally’ deleting rows.

What approach do you adopt to deny all users from deleting data in a table?