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
