Using a DDL Trigger to Block Schema Changes in SQL Server

Problem

Every DBA I’ve worked with, and I’ll include myself in this category, has at one time or another run a sql statement against a database thinking they were in some other environment when they were really in production. It’s really too bad there isn’t like a 10 second delay after you hit F5 where you can still somehow cancel the statement from running because we all know that it’s usually a split second after you hit the key that you realize you made the mistake. This tip will look at a method you can use to add an extra layer of security to help prevent some of these mistakes from happening.

Solution

The first solution that came to mind when I thought about this problem in the past was to just add explicit DENY permissions on all the objects for all users. The main issue with this approach is that it doesn’t prevent logins with sysadmin privileges from making changes. Luckily for us with the release of SQL Server 2005 came DDL triggers. I’ve found a fair bit has been written regarding how to audit and track database changes, but I am more concerned with finding a way to prevent inadvertent changes from occurring.

Here are some related tips on auditing:

The first step we have to consider when creating our DDL trigger is for what events do we want this trigger to fire. In this case we will just consider blocking any ALTER or DROP statements on the following object types: PROCEDURE, TABLE, FUNCTION, INDEX, VIEW, and TRIGGER. Here is a complete list of events along with the corresponding TSQL for the beginning of our DDL trigger code.

CREATE TRIGGER [block_ddl]
    ON DATABASE
    FOR ALTER_PROCEDURE, DROP_PROCEDURE, 
        ALTER_TABLE, DROP_TABLE, 
        ALTER_FUNCTION, DROP_FUNCTION, 
        ALTER_INDEX, DROP_INDEX, 
        ALTER_VIEW, DROP_VIEW,
        ALTER_TRIGGER, DROP_TRIGGER
    AS

Next we need a way to capture information about the event that caused the trigger to fire. To get this information we can use the EVENTDATA() function. For this example, the only item we need to be concerned with is the EventType, which is part of every event. Using an IF statement with this function we can check the event type as follows.

IF EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') LIKE '%DROP%'
IF EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') LIKE '%ALTER%'

Now that we’ve captured the fact that a DROP or ALTER was executed all that’s left to do is ROLLBACK the transaction and return a descriptive error message. To handle the error messages we are going to create a couple user-defined error messages using sp_addmessage. You can query the sys.messages system table to get a complete list of all the user-defined messages.

sp_addmessage 50001,16,
              'Objects cannot be dropped from this database, please contact your DBA',
              @with_log=FALSE,@lang='us_english'
go
sp_addmessage 50002,16,
              'Objects cannot be altered in this database, please contact your DBA',
              @with_log=FALSE,@lang='us_english'
go

Now that we have all the pieces, let’s create this trigger and test it using a TABLE object to make sure we cannot DROP or ALTER the table once the trigger is in place. You can find the complete trigger code listing and test script here. Please note that if you run this test script in its entirety it also cleans up all the objects at the end. The following output shows the error messages you would receive when trying to DROP or ALTER the table with the trigger enabled.

Msg 50001, Level 16, State 1, Procedure block_ddl, Line 14
Objects cannot be dropped from this database, please contact your DBA
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
Msg 50002, Level 16, State 1, Procedure block_ddl, Line 20
Objects cannot be altered in this database, please contact your DBA
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Once this trigger is in place it now becomes a 2 step process if you want to make any schema changes (other than new object creation). First you have to disable this trigger (DISABLE TRIGGER [block_ddl] ON DATABASE) and then you will be able to run your DROP and ALTER statements without error. I have intentionally left out blocking CREATE statements since these do not usually cause as disruptive an issue as does DROPPING or ALTERING an object inadvertently. Keep in mind that once this trigger is disabled anyone with sufficient rights can make changes, so it’s best to re-enable this trigger as soon as you are done.

Next Steps

Expand this trigger to block other events
Using /EVENT_INSTANCE/LoginName add logic to the trigger to allow only certain users to ALTER/DROP objects without having to disable the trigger
Using /EVENT_INSTANCE/ObjectName add logic to the script to only block actions against your most important objects

Resources

Community

Subscribe