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')
BEGIN
DROP TRIGGER StopAndNotifyDDL
ON DATABASE;
END;
GO
CREATE TRIGGER StopAndNotifyDDL
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS -- Captures all events in the database
AS
BEGIN
/*
Database DDL Trigger by Julian Kuiters 2005
http://www.juliankuiters.id.au
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
BEGIN
-- Rollback the DDL changes
ROLLBACK;
-- 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)
WITH
LOG, -- Make sure this error is in the SQL Log
NOWAIT, -- Return the error now
SETERROR; -- Sets @@ERROR and ERROR_NUMBER
END
END;
GO
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 EXECUTED BY: julian ----- BEGIN DDL Statement -------------- create table tablec ( col1 int) ----- END DDL Statement ---------------- CREATE_TABLE 2005-05-23T11:40:25.203 58 SQL05 julian julian scratch dbo tablec TABLE 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 —————-