Bank Holiday Fun - DDL Trigger

Following on from my previous post on triggers, I had made a mental note to make sure I did a post about a DDL trigger I use.  As a DBA I am a gatekeeper to the companies SQL Server estate, I would love to say that every instance is locked down tighter than Fort Knox but this is not true.  The reality is that there are inevitably permissions nested that give a level of access that exceeds what the person actually requires to fulfill their role.  Sure I could just cart blanch everything I deem as being excessive and go on a REVOKE spree, but there would be a whole host of political repercussions as a result.  Rightly or Wrongly, the cold harsh reality is that some of these permissions will remain.

As a safety net I use a DDL Trigger in every single production database to notify me if someone has abused the privilege of having elevated permission.  It is worth highlighting that while I was creating the script used in this post I ran into the 2000 line limit of the undocumented sp_msforeachdb stored procedure and was just about to revert to using a while loop when @sqlchicken pointed out I should look at using an improved version created by @AaronBertrand.  You can find the sp_foreachdb procedure here. It is so much more flexible than the original Microsoft system procedure and one that I have now rolled out to our SQL Estate.  I will also be going back and updating all the routines I have that use sp_msforeachdb and replacing them with sp_foreachdb.

The below script will create a DDL Trigger trgDDLTableModification in every user database which will fire for the below statements;

ALTER_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER

You will need to change the below two lines accordingly

@profile_name = 'DBA',
@recipients   = 'Chris@SQLServer365.com',

/*

      -----------------------------------------------------------------

      DDL Trigger

      -----------------------------------------------------------------

    

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE master;

GO

-- Drop trigger in all user databases

EXEC sp_foreachdb

@command = 'USE ?;

IF EXISTS (SELECT 1 FROM sys.triggers WHERE parent_class_desc = ''DATABASE'' AND name = ''trgDDLTableModification'')

DROP TRIGGER trgDDLTableModification ON DATABASE;

GO',

@user_only = 1,

@print_command_only = 1;

GO

-- Print the create trigger command for all user databases

EXEC sp_foreachdb

@command =

'USE ?;

GO

CREATE TRIGGER trgDDLTableModification ON DATABASE

FOR ALTER_TABLE, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER

AS

BEGIN

    -- SET options

    SET NOCOUNT ON;

     

      -- Declare variables

    DECLARE @xmlEventData XML;

    DECLARE @DDLStatement VARCHAR(MAX);

    DECLARE @Msg VARCHAR(MAX);

    DECLARE @MailSubject  VARCHAR(255);

    DECLARE @DatabaseName VARCHAR(255);

    DECLARE @User NVARCHAR(50);

    -- Build variables from event data

    SELECT @xmlEventData = EVENTDATA();

    SET @DDLStatement = @xmlEventData.value( ''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'', ''nvarchar(max)'' );

    SET @DatabaseName = CONVERT(VARCHAR(150), @xmlEventData.query(''data(/EVENT_INSTANCE/DatabaseName)''));

    SET @User = CONVERT(VARCHAR(150), @xmlEventData.query(''data(/EVENT_INSTANCE/LoginName)''));

    IF CHARINDEX(''DROP TRIGGER trgDDLTableModification'',@DDLStatement) != 1

        AND CHARINDEX(''ALTER TRIGGER trgDDLTableModification'',@DDLStatement) != 1

    BEGIN

        SELECT @Msg                 = ''ALERT FIRED AS A RESULT OF A DDL TABLE LEVEL EVENT IN DATABASE: '' + @DatabaseName + CHAR(13) + CHAR(13)

                            + ''*** Start DDL Statement ***'' + CHAR(13) + CHAR(13)

                            + @DDLStatement + CHAR(13) + CHAR(13)

                            + ''*** End DDL Statement ***'' + CHAR(13) + CHAR(13)

                            + ''*** Start Event Data ***'' + CHAR(13) + CHAR(13)

                            + CAST(@xmlEventData AS NVARCHAR(MAX)) + CHAR(13) + CHAR(13)

                            + ''*** End Event Data ***'' + CHAR(13) + CHAR(13) ;                         

        SELECT @MailSubject = ''DDL TABLE LEVEL EVENT MODIFICATION DETECTED ON: '' + @@SERVERNAME;

           

            -- Send mail alert

        EXEC msdb.dbo.sp_send_dbmail

                  @profile_name     = ''DBA'',

            @recipients   = ''Chris@SQLServer365.com'',

            @subject      = @MailSubject,

            @body         = @Msg ,

            @importance   = ''high'';

    END

END;

GO',

@user_only = 1,

@print_command_only = 1;

GO

I have used the @print_command_only = 1 for both the drop and create statements for consistency because 'CREATE TRIGGER' must be the first statement in a query batch, so the second execution of sp_foreachdb would fail otherwise.  All you need to do once run is copy the results from the messages window paste them in a new query window and execute.

Enjoy!

Chris

Trigger Status

Have you ever spent hours looking at an issue only to have your investigation hindered by a trigger?  I know I have and on more than one occasion!  This little script can be added to a SQL Agent Job and scheduled as you require to email an operator with a list of all triggers for all user databases, the table they are on and the status.

/*

      -----------------------------------------------------------------

      Trigger Status

      -----------------------------------------------------------------

    

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE master

GO

-- Declare variables

DECLARE @EmailProfile VARCHAR(255)

DECLARE @EmailRecipient VARCHAR(255)

DECLARE @EmailSubject VARCHAR(255)

-- Set variables

SET @EmailProfile = 'SQLReports';

SET @EmailRecipient = 'Chris@SQLServer365.co.uk';

--Drop temporary table if exists

IF OBJECT_ID('tempDB.dbo.#TriggerStatus') IS NOT NULL

    DROP TABLE #TriggerStatus ;

   

-- Create temporary table   

CREATE TABLE #TriggerStatus

(

      DatabaseName SYSNAME,

      TableName VARCHAR(255),

      TriggerName VARCHAR(255),

      TriggerStatus VARCHAR(8)

);

-- Insert triggers

INSERT INTO #TriggerStatus

EXEC sp_msforeachdb

'

IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')

BEGIN

USE [?];

SELECT  DB_NAME() AS DatabaseName,

        OBJECT_NAME(parent_id) AS TableName,

        name AS TriggerName,

        CASE is_disabled

          WHEN 0 THEN ''Enabled''

          ELSE ''Disabled''

        END AS TriggerStatus

FROM    sys.triggers WITH ( NOLOCK )

WHERE   is_ms_shipped = 0

        AND parent_class = 1;

END'

-- Check for unused indexes

IF EXISTS ( SELECT  1

            FROM #TriggerStatus)

    BEGIN

        DECLARE @tableHTML NVARCHAR(MAX); 

        SET @tableHTML = N'<style type="text/css">'

            + N'.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} '

            + N'.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} '

            + N'body {font-family: Arial, verdana;} '

            + N'table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} '

            + N'td{background-color:#F1F1F1; border:1px solid black; padding:3px;} '

            + N'th{background-color:#99CCFF; border:1px solid black; padding:3px;}'

            + N'</style>' + N'<table border="1">' + N'<tr>'

            + N'<th>DatabaseName</th>'

            + N'<th>TableName</th>'

            + N'<th>TriggerName</th>'

            + N'<th>TriggerStatus</th>'

            + N'</tr>'

            + CAST(( SELECT td = DatabaseName,

                            '',

                            td = TableName,

                            '',

                            td = TriggerName,

                            '',

                            td = TriggerStatus,

                            ''                       

                     FROM   #TriggerStatus

                   FOR

                     XML PATH('tr') ,

                         TYPE

                   ) AS NVARCHAR(MAX)) + N'</table>'; 

     

            -- Set subject

            SET @EmailSubject = 'Trigger Status Report For ' + @@SERVERNAME

           

            -- Email results 

        EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,

            @recipients = @EmailRecipient, @subject = @EmailSubject,

            @body = @tableHTML, @body_format = 'HTML'; 

    END

    GO

Remember, fully understanding your environment, the features you use and what is the norm is something that pays dividends when things go bad.

Enjoy!

Chris