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:
CREATE TABLE SitesTable (
INSERT SitesTable VALUES(1, 'SqlServerCurry.com')
INSERT SitesTable VALUES(2, 'DevCurry.com')
INSERT SitesTable VALUES(3, 'DotNetCurry.com')
-- Trigger by SqlServerCurry.com
CREATE TRIGGER noSitesDelete ON SitesTable
IF USER_NAME() <> 'Sam'
RAISERROR('You cannot delete SitesTable!',16,1)
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?