Detecting Database Option Changes with DDL Triggers

Steve Jones, 2012-06-05 (first published: 2012-05-29)

One of the keys to managing a large production SQL Server environment is being aware of changes that are taking place in the environment, and preventing potentially harmful changes. There are any number of ways to do this: triggers, auditing, PMB, third party monitoring, and more.

In this post, I want to look at a quick way to detect issues with databases using DDL triggers. Specifically, I will build a quick trigger that responds to the ALTER DATABASE event.

Tracking Changes

In general, I try to avoid those options that severely limit my flexibility. I dislike trying to enforce every possible rule I create since I understand that IT environments often evolve and change, and the hard rules you have today may not apply tomorrow. I also realize that most of the time the hard rules have exceptions to them for various reasons and it’s much easier to manage a set of instances if you expect that the might not have the same requirements.

I do want to be informed of changes, and one option is a DDL trigger that responds to a particular event. DDL triggers have a large list of events that will trigger them, of which the ALTER DATABASE is one.

We build the trigger by giving it a name, scope and an event. In this case, we’ll start with this template

CREATE TRIGGER [name]

ON ALL [scope]

FOR [event]

You can change the scope and events as needed. For me, I need the Server level scope (database changes are a server instance event) and then the ALTER_DATABASE event. From there, it’s pretty much normal T-SQL coding.

The data comes back from the EventData() function as an XML fragment, so in the trigger, I need to parse out the particulars that I care about.

The code I’ll use is this:

CREATE TRIGGER DBAAudit_ALTER_Database
ON ALL Server
FOR ALTER_Database  -- Captures a Create Database Event
AS
     
DECLARE
  @EventTime datetime
, @ServerName varchar(200)
, @LoginName varchar(200)
, @DatabaseName varchar(200)
, @TSQL varchar(2000)
, @event XML
select @event = EVENTDATA()
SELECT @ServerName = @event.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(200)')
SELECT @EventTime = @event.value('(/EVENT_INSTANCE/PostTime)[1]','datetime' )
SELECT @LoginName = @event.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(200)' )
SELECT @DatabaseName = @event.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(200)' )
SELECT @TSQL = @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(2000)' )
Print 'Database ' + @Servername + '.' + @DatabaseName + ' was altered by ' + @LoginName
Print 'Command: ' + @TSQL

When I create this on my instance, it is stored in the Server Objects \ Triggers area on my server. Just like any other object, I can right click and perform all kinds of actions in SSMS.

ddltrigger_a

When I execute a change on a database, such as setting a database to read only with this:

alter database dba_admin set READ_ONLY

I get this in the messages tab (from the Print statement)

Database DKRSQL2012.dba_admin was altered by DKRSQL2012\Steve

Command: alter database dba_admin set READ_ONLY

If I set the database back to read_write, I get this:

Database DKRSQL2012.dba_admin was altered by DKRSQL2012\Steve

Command: alter database dba_admin set READ_Write

This is a nice example, but in a real system, I’d use a database for tracking these changes and store the information from the event in a table. Instead of a PRINT, I’d insert data into a table that tracks changes.

Filed under: Blog Tagged: auditing, sql server, syndicated, T-SQL

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads