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.

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