Trigger on delete of ANY table of Database?

  • Hi

    I know its possible to prevent the execution of deletion command for certain tables by adding a instead of trigger on those tables.

    But, how do you go about triggering / (avoiding /recording) the deletion of ANY table in a database? Probably using systables?

    (Without considering - Not granting permission for delete to users)

  • You need a trigger on every table. You can write up some code based on sys.tables to create the triggers, but there will have to be one for each table that you want to check/prevent deletion on.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Are you talking about row deletion or table deletion (DROP)?

    Scott Pletcher, SQL Server MVP 2008-2010

  • Row deletion.

  • Yes, you need a trigger on every table.

    You could try DENYing them DELETE (which is stronger than just not GRANTing them permission) as a good first step. But that's not a guarantee of preventing them DELETE altogether, so you would likely still want the triggers.

    Scott Pletcher, SQL Server MVP 2008-2010

  • What do you mean by DENY?

  • There is a separate DENY command:

    DENY DELETE ON <tablename> TO <user/role>

    I think you can even specify multiple tablenames and/or users and roles in the same command.

    [EDIT: You cannot specify multiple tablenames in one command, only multiple users/roles.]

    DENY overrides GRANTs and will still prevent DELETE from that table, unless a user override/context switch has occured (such as "EXEC AS").

    DENY is strong, but you probably still can't rely on it 100% (?).

    Scott Pletcher, SQL Server MVP 2008-2010

  • http://msdn.microsoft.com/en-us/library/ms188338%28SQL.90%29.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • scott.pletcher (7/7/2010)


    DENY overrides GRANTs and will still prevent DELETE from that table, unless a user override/context switch has occured (such as "EXEC AS").

    Or the user is sysadmin.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have tested DENY, even up to db_owner, and it works (interesting that it worked even for db_owner).

    But my extensive testing was under SQL 2000. Naturally you'll want to do your own testing for various security levels.

    DENY will certainly be pretty effective even without other steps. I would use it even with triggers, since it's way less overhead to catch it in the security check phase than in a trigger.

    Scott Pletcher, SQL Server MVP 2008-2010

  • scott.pletcher (7/7/2010)


    I have tested DENY, even up to db_owner, and it works (interesting that it worked even for db_owner).

    Yes, but sysadmins (and by that I mean only members of the syadmin fixed server role) bypass all security checks.

    http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/03/06/control-server-vs-sysadmin-membership.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • DENY doesn't work with sysadmins, as Gail pointed out.

    DENY won't prevent db_owners from dropping the DENY itself (db_owner implies CONTROL).

    I experienced some strange things with DENY, so I'd be careful.

    It once happened to me that DENY ALTER worked for a windows login and didn't work for a SQL login with identical permissions. It was a SQL Server 2005 SP2. I was never able to reproduce it, but I'm quite sure it did happen.

    -- Gianluca Sartori

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply