Audit DDL Operations

One of the great new features in SQL Server 2005 is the ability to create database triggers. Using this new functionality, you can create a table to store all DDL changes done to a given DB along with what the change was, who did it, and when.
You may want to keep this table stored in a separate DB, perhaps adding a column for which DB had the change occur. The WITH ENCRYPTION allows you to encrypt the trigger, so that prying eyes can not see what it is doing and either edit the trigger or find a way around it.
The code:
CREATE TABLE AuditDDLOperations
(
OpID int NOT NULL identity
CONSTRAINT AuditDDLOperationsPK
PRIMARY KEY CLUSTERED,
LoginName sysname NOT NULL,
UserName sysname NOT NULL,
PostTime datetime NOT NULL,
EventType nvarchar(100) NOT NULL,
DDLOp nvarchar(2000) NOT NULL
)
GO
CREATE TRIGGER AuditAllDDL
ON DATABASE
–WITH ENCRYPTION
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT AuditDDLOperations
(LoginName, UserName, PostTime, EventType, DDLOp)
VALUES (SYSTEM_USER, CURRENT_USER, GETDATE()
, @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(100)’)
, @data.value(‘(/EVENT_INSTANCE/TSQLCommand)[1]’, ‘nvarchar(2000)’) )
RETURN
GO
Advertisements

About Rodibidably

Jeff Randall is a frequent volunteer for free-thought organizations, including the Center For Inquiry – DC. Having been blogging since January 2008, he decided that a community of bloggers would be an interesting new experience (or at the very least a fun way to annoy his friends into reading his posts more frequently). Since finding out about about the existence of, and then joining, the atheist/skeptic community in 2007 he has been committed to community activism, critical thinking in all aspects of life, science, reason, and a fostering a secular society.
This entry was posted in SQL Server. Bookmark the permalink.

One Response to Audit DDL Operations

  1. StreetM says:

    Hi I like this site im glad you updated it . I like the new options.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s