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?

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook