2

I want to restrict deletion of specific row. For example if someone wants to truncate a table or delete all the records from table then an error message should be thrown, for particular id it is not allowed. I have used trigger for the same but it is of no use, as it does not fire at truncate

Create TRIGGER vendorTrigger
    ON [Vendor]
    INSTEAD OF DELETE
AS
    IF EXISTS(SELECT * FROM Vendor WHERE VendorId = 515)
    BEGIN
        RAISERROR ('Is not allowed to delete VendorId: 515 as it is of type corpnet',16, 1)  
    END
    ELSE
    BEGIN
       Delete from Vendor Where VendorId in (Select VendorId From Vendor)
    END
  • Welcome to this site, have a look at DDL triggers. – McNets Sep 27 at 6:59
  • And you forget about possible DROP and re-CREATE table... – Akina Sep 27 at 7:15
  • @McNets triggers doesn't seems to work in case of Truncate. – Janvi Sep 27 at 8:39
  • No, it don't... – McNets Sep 27 at 8:40
0

If you are on SQL Server 2016 or higher you might want to consider Row-Level Security

If not you can restrict access to specific rows using views. Useful links:

Stairway to SQL Server Security Level 10: Row-Level Security

SQL Server 2016 Row Level Security

Restricting and monitoring SQL Server data access with SQL views and stored procedures

  • 1
    I don't know OP nationality, but let me suggest to change Row-Level Security link to the english site. – McNets Sep 27 at 7:48
4

TRUNCATE TABLE requires ALTER permission on the table. The best way to prevent truncating a table is to revoke that permission.

What’s more TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

See more on TRUNCATE TABLE (Transact-SQL)

4

I agree that permissions is the preferred way to handle certain operations (like TRUNCATE TABLE).

If that doesn't cut it, then specifically for TRUNCATE TABLE, you can have a foreign key referring from some other table to this table. That other table can be a dummy table and it can be empty. You can even disable the FK. The presence of the FK prohibits TRUNCATE TABLE.

But, of course, with enough privileges, that user can still do things like removing the FK, droppting and re-creating the table. Etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.