New or Changed Table Alert with DDL Triggers

— By Lori Brown  @SQLSupahStah

If you are the usual DBA, you are often the last person to know when something new has been added to the databases you are managing. After finding that some people in an organization were abusing their access to a certain database, I decided to create a TSQL DDL trigger at the database level to alert me when tables are created or changed. This DDL trigger could also be created at the server level meaning that it would alert for the same events no matter what database the event happens in. An alternative to an immediate alert is to log the data captured from the alerts to a table in an administrative database and then possibly reporting on that later on.

For the notification that was immediately needed a simple e-mail alert was sufficient so I made it into an easily readable html format. Once in place, you should receive an e-mail for each new object created or altered. There are a lot of events you can set up DDL triggers on. A full list of them can be found on MSDN at: https://msdn.microsoft.com/en-us/library/bb522542.aspx

In my code below I have used an administrative database named ADMIN to set up the DDL trigger on and to create and alter tables on to demonstrate that the e-mail alert works. Of course, you will need to have Database Mail enabled and working and you will need to know a mail profile to use as well.

USE ADMIN

GO

CREATE TRIGGER NewChangedTblAlert

ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE

AS

BEGIN

SET NOCOUNT ON

DECLARE @emailbody NVARCHAR(MAX)

DECLARE @emailsubj NVARCHAR(100)

DECLARE @EventDate DATETIME

DECLARE @EventType NVARCHAR(100)

DECLARE @EventDDL NVARCHAR(MAX)

DECLARE @EventXML XML

DECLARE @DatabaseName NVARCHAR(255)

DECLARE @SchemaName NVARCHAR(255)

DECLARE @ObjectName NVARCHAR(255)

DECLARE @HostName VARCHAR(64)

DECLARE @IPAddress VARCHAR(32)

DECLARE @ProgramName NVARCHAR(255)

DECLARE @LoginName NVARCHAR(255)

SET @EventXML = EVENTDATA()

SET @IPAddress = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID)

SELECT @EventDate = GETDATE(),

@EventType = @EventXML.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘NVARCHAR(100)’),

@EventDDL = @EventXML.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘NVARCHAR(MAX)’),

@DatabaseName = DB_NAME(),

@SchemaName = @EventXML.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘NVARCHAR(255)’),

@ObjectName = @EventXML.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘NVARCHAR(255)’),

@HostName = HOST_NAME(),

@ProgramName = PROGRAM_NAME(),

@LoginName = SUSER_SNAME()

— Build e-mail body

SET @emailbody=‘<p> A table has been created or altered in the ‘+@DatabaseName+‘ database.</p>’

— add table attributes

SET @emailbody=@emailbody+‘<table border=”2″ cellspacing=”2″ cellpadding=”2″>

<tbody align=”left” style=”font-family:Arial; font-weight:700; font-size: 12; color: red;”

<TR><TD colspan=9 >’

— add table title

SET @emailbody=@emailbody+‘New or Changed Table </TD></TR></tbody>’

— add column headers

SET @emailbody=@emailbody+ ‘<tbody align=”left” style=”font-family:Arial; font-size: 11;”

<TR><TH>DDL Type</TH><TH>DDL Statement</TH>

<TH>Schema</TH><TH>DatabaseName</TH><TH>TableName</TH><TH>LoginName</TH>

<TH>HostName</TH><TH>IPAddress</TH><TH>ProgramName</TH></TR></tbody>’

— add data

SELECT @emailbody=@emailbody +‘<tbody align=”left” style=”font-family:Arial; font-size: 11;”

<TR><TD>’+@EventType+‘</TD><TD>’+@EventDDL+‘</TD><TD>’+

@SchemaName+‘</TD><TD>’+@DatabaseName+‘</TD><TD>’+@ObjectName+

‘</TD><TD>’+@LoginName+‘</TD><TD>’+@HostName+‘</TD><TD>’+

@IPAddress+‘</TD><TD>’+@ProgramName+‘</TD></TR></tbody>’

— subject

SET @emailsubj = ‘New or Changed Tables in ‘+@DatabaseName+‘ Database’

— Send an html formatted e-mail to notify ddl events

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘DBMailProfile’, — Use valid database mail profile here

@recipients = ‘somebody@company.com’, — Use valid email address here

@subject = @emailsubj,

@body = @emailbody,

@body_format = ‘HTML’

SET NOCOUNT OFF

END

GO

To see a list of DDL triggers, you can query the sys.triggers table.

SELECT * FROM sys.triggers

Now we need to test that an alert is sent by creating and altering tables.

USE ADMIN

GO

—  create 2 tables, should send e-mail for each

CREATE TABLE Table1

(

COL1 INT,

COL2 VARCHAR(2)

)

GO

CREATE TABLE Table2

(

COL1 INT,

COL2 VARCHAR(2)

)

GO

–Generate an alter table event, should send e-mail

ALTER TABLE Table1

ADD COL3 VARCHAR(6)

GO

Here is an example of the e-mail alert that is sent.

Once again, it would be really easy to log events to a table. Just create a table to hold the events. You can even create this in a different database than the one that has the DDL trigger created on it:

USE MonitorDB

GO

CREATE TABLE dbo.DDLEvents

(       ID          INT IDENTITY(1,1) NOT NULL,

EventDate    DATETIME NOT NULL,

EventType    NVARCHAR(64),

EventDDL     NVARCHAR(MAX),

EventXML     XML,

DatabaseName NVARCHAR(255),

SchemaName   NVARCHAR(255),

ObjectName   NVARCHAR(255),

HostName     VARCHAR(64),

IPAddress    VARCHAR(32),

ProgramName  NVARCHAR(255),

LoginName    NVARCHAR(255)

)

Then add this insert statement to the trigger after all the info for the variables has been gathered. That would be either just before creating the e-mail body or maybe after sending the e-mail alert.

INSERT INTO MonitorDB.dbo.DDLEvents (

EventDate,

EventType,

EventDDL,

EventXML,

DatabaseName,

SchemaName,

ObjectName,

HostName,

IPAddress,

ProgramName,

LoginName

)

VALUES (

@EventDate,

@EventType,

@EventDDL,

@EventXML,

@DatabaseName,

@SchemaName,

@ObjectName,

@HostName,

@IPAddress,

@ProgramName,

@LoginName

)

Now you can report on the types of change activity that you log. I’ll leave it to you to play with logging and reporting. Have fun with it!

I always like to provide some form of cleanup should you need it. You can either disable or drop the DDL trigger by using the statements below:

— Disable trigger

USE ADMIN

GO

DISABLE TRIGGER NewChangedTblAlert ON DATABASE

GO

— Drop trigger

USE ADMIN

GO

DROP TRIGGER NewChangedTblAlert ON DATABASE

GO

If you need to implement DDL triggers, I hope this has been helpful. For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!