locked
Bypass/Skip truncate statement executed on sql server RRS feed

  • Question

  • Hello,

    I want a functionality that if someone just executed truncate table mytable on sql server, then it should not get truncated, I checked I found there are DDL triggers for drop table and alter table but when i execute truncate command the DDL triggers does not get executed, Another option will be remove sysadmin permissions to user and some specific permissions only,

    But I want to know is there any other way to handle this scenario?

    Thanks in advance,

    Thanks,

    Dhiraj

    Tuesday, January 14, 2014 2:51 AM

Answers

  • Hello,

    TRUNCATE TABLE will not fire triggers:

    "TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions"

    Source:http://technet.microsoft.com/en-us/library/ms177570.aspx

    I would not allow any user to have that ability by default - nor would any login have sysadmin except for my DBA team. If you wanted to be as secure and limit scope as possible I would create a signed stored procedure that runs under the context of a user with no login. Give the users that need access to truncate those tables execute on the stored procedure.

    Erland has a great paper on this: http://sommarskog.se/grantperm.html


    Sean Gallardy | Blog | Twitter

    • Proposed as answer by SathyanarrayananS Tuesday, January 14, 2014 3:37 AM
    • Marked as answer by Sofiya Li Monday, January 20, 2014 1:27 AM
    Tuesday, January 14, 2014 3:08 AM

All replies

  • Refer below link http://blog.sqlauthority.com/2010/12/20/sql-server-securing-truncate-permissions-in-sql-server/ -Prashanth
    Tuesday, January 14, 2014 3:03 AM
  • Hello,

    TRUNCATE TABLE will not fire triggers:

    "TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions"

    Source:http://technet.microsoft.com/en-us/library/ms177570.aspx

    I would not allow any user to have that ability by default - nor would any login have sysadmin except for my DBA team. If you wanted to be as secure and limit scope as possible I would create a signed stored procedure that runs under the context of a user with no login. Give the users that need access to truncate those tables execute on the stored procedure.

    Erland has a great paper on this: http://sommarskog.se/grantperm.html


    Sean Gallardy | Blog | Twitter

    • Proposed as answer by SathyanarrayananS Tuesday, January 14, 2014 3:37 AM
    • Marked as answer by Sofiya Li Monday, January 20, 2014 1:27 AM
    Tuesday, January 14, 2014 3:08 AM
  • If you are evil, you could add a table which has a foriegn-key constraints which references this table. That disallows TRUNCATE TABLE (to the chagrin of many).


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, January 14, 2014 10:19 PM