SQL Server 2005 DDL Triggers
time to read 8 min | 1410 words

I was at the Israeli Security User Group meeting today and I met with Justin, who mentioned his favoirate tough question for now is: "Can a DDL Trigger in SQL Server 2005 prevent dropping a DDL Trigget?"

It turns out that it can, but there is a catch. A DDL trigger is not called if the trigger that is being dropped is the same trigger. This mean that to implement this functionality we need something like:

CREATE TRIGGER Prevent_Drop_DDL_Trigger

ON DATABASE

FOR DROP_TRIGGER

as

      if PATINDEX ('%on database%', EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) > -1

      BEGIN

            PRINT 'No deleting of DDL Triggers!'

            ROLLBACK

      END

GO

CREATE TRIGGER Prevent_Drop_DDL_Trigger_Second

ON DATABASE

FOR DROP_TRIGGER

as

      if PATINDEX ('%on database%', EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')) > -1

      BEGIN

            PRINT 'No deleting of DDL Triggers!'

            ROLLBACK

      END

GO

Now, let's try dropping one of the triggers:

drop

trigger Prevent_Drop_DDL_Trigger on database

This will call the Prevent_Drop_DDL_Trigger_Second which will fail the query. Prevent_Drop_DDL_Trigger is not called for its own drop, by the way.

Be aware that this means that in order to remove those triggers you need to ALTER one of them, DROP the second one, and then DROP the first one. It's possible to get into a situation where the two triggers doesn't allow drop / alter on either one, which potentially can lead to a trigger that you can modify without dropping the database.

I would classify this behavior as Evil (TM).

 

Tweet Share Share 0 comments
Tags: