SQL Server 2005 allows you to create DDL Triggers in a database, that fire whenever someone attempts to change the structure of your database.

Ever accidentally created tables / views / sprocs in the master database? Well Greg Low published a trigger you can place in the master database to prevent changes there. Apparently Greg’s original version of the trigger was a show stopper. After he applied it the first time, there was no way to undo it. Attempting to disable the trigger fired the trigger, which rolled back the disable. The version you see on his site now uses a CHARINDEX to see if you are attempting to change the trigger itself, so that you can disable the trigger when you need to. A few people noted that you can hack around this trigger. Because of the use of CHARINDEX(…) = 0 you can still apply changes by hiding — ProtectMasterDDL in your DDL command.

Darshan Singh has posted a update to the trigger, which uses less code, and also grabs the original user and DDL statement from the EVENTDATA() It doesn’t however protect or prevent triggers, so you can still cause some damage.

From a security standpoint. I think you not only want to prevent people from mucking up your master database, you need to report it to someone. Raising an error that is logged in the SQL Logs is vital. As well as notifying your DBA / System Teams. This sort of thing could be used as an early detection of an unwanted intruder (or just a wayward developer).

So here is the DDL trigger I’ve crafted. You can use it in any database. You will need SQLiMail installed in the MSDB database if you want email notifications (comment out the mail section if you don’t want emails sent). Don’t forget to change the @profile_name and @recipients so that you’ll get the emails.

IF EXISTS (SELECT name FROM sys.triggers
       WHERE parent_class = 0 AND 
       name = 'StopAndNotifyDDL')

FOR  DDL_DATABASE_LEVEL_EVENTS  -- Captures all events in the database

        Database DDL Trigger by Julian Kuiters 2005
        Version 1.1 25 May 2005
        Use this trigger to prevent changes to a database (your master database for example), and to
        receive email notifications when a DDL statement is stopped.

    SET NOCOUNT ON;                         -- Prevents the trigger from creating another record set

    DECLARE @xmlEventData XML;              -- To hold EVENTDATA() xml output
    DECLARE @DDLStatement nvarchar(max);    -- To hold the DDL statement that was attempted
    DECLARE @UserName     nvarchar(max);    -- To hold the user that execute the command
    DECLARE @DatabaseName nvarchar(max);    -- To hold the database that was trying to be changed
    DECLARE @ErrorMsg     nvarchar(max);    -- To hold the error message we create
    DECLARE @MailSubject  nvarchar(2048);   -- To hold the Subject for the Mail.

    SELECT @xmlEventData = EVENTDATA();     -- Get the EVENTDATA()

    -- Get the DDL Statement that was attempted from the eventdata.
    SET @DDLStatement = @xmlEventData.value( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)' );

    -- If the DDL Statement is trying to make changes to anything other than this trigger
    IF CHARINDEX('DROP TRIGGER StopAndNotifyDDL',@DDLStatement) != 1
        AND CHARINDEX('ALTER TRIGGER StopAndNotifyDDL',@DDLStatement) != 1

        -- Rollback the DDL changes

        -- Get the user that attempted the change
        SET @UserName = @xmlEventData.value( '(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)' );

        -- Get the database that the change was to affect
        SET @DatabaseName = @xmlEventData.value( '(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)' );

        -- Create the Error Message
        SELECT @ErrorMsg  =   'DATABASE:    ' + @DatabaseName + CHAR(13) 
                            + 'EXECUTED BY: ' + @UserName + CHAR(13) 
                            + '----- BEGIN DDL Statement --------------' + CHAR(13) + CHAR(13) 
                            + @DDLStatement + CHAR(13) + CHAR(13) 
                            + '----- END DDL Statement ----------------' + CHAR(13) + CHAR(13) 
                            + CAST(@xmlEventData as nvarchar(max));
        SELECT @MailSubject = 'StopAndNotifyDDL Trigger caught DDL Statement on ' + @DatabaseName;

        -- Print the message to the user
        PRINT 'StopAndNotifyDDL Trigger has prevented your statement from running.' + CHAR(13) 
               + 'You must disable the StopAndNotifyDDL trigger before you can make these changes.' + CHAR(13) 
               + @ErrorMsg + CHAR(13) 
               + CHAR(13) 
               + 'Notifying Administrators...';

        -- Send an email
        -- NOTE: You need SQLiMail installed in the MSDB database.
        -- Remember: Change the @profile_name and @recipients to suit your environment.
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'Server SQLiMail Profile' 
            , @recipients   = 'dba@mydomian.x'
            , @subject      = @MailSubject
            , @body         = @ErrorMsg  
            , @importance   = 'high'; 

        -- Raise an error
        SET @ErrorMsg = REPLACE(@ErrorMsg,CHAR(13),' ')                   -- Remove carriage returns before raiserror 
        RAISERROR ( 'StopAndNotifyDDL Trigger: %s', 18, 1, @ErrorMsg) 
            LOG,        -- Make sure this error is in the SQL Log
            NOWAIT,     -- Return the error now
            SETERROR;   -- Sets @@ERROR and ERROR_NUMBER



Once you have applied the trigger to a database, any DDL statement should produce output similar to the following (XML has been formatted to make it easier to read):

StopAndNotifyDDL Trigger has prevented your statement from running.
You must disable the StopAndNotifyDDL trigger before you can make these changes.
DATABASE:    scratch
----- BEGIN DDL Statement --------------

create table tablec ( col1 int)

----- END DDL Statement ----------------

		create table tablec ( col1 int)

Notifying Administrators...
Mail successfully queued. No user action is required.

Msg 3609, Level 16, State 2, Line 1
Transaction ended in trigger. Batch has been aborted.
Msg 50000, Level 18, State 1, Procedure StopAndNotifyDDL, Line 68
StopAndNotifyDDL Trigger: DATABASE: scratch EXECUTED BY: julian —– BEGIN DDL Statement ————– create table tablec ( col1 int) —– END DDL Statement —————-