One of the most amazing features to an old SQL Server 4.2 guy was the addition of DDL triggers to the server. As with any trigger, these can be problematic in that they can overload a server, and they ALWAYS fire, so you can cause yourself problems, but in terms of auditing, I think they’re great.

As a quick example, perhaps you’re worried about new logins, as I talk about in my AlwaysOn and Contained Databases in SQL Server 2012 presentation. You want to capture when a new login is created. You can do this with a DDL trigger like this one:

USE master
GO
CREATE trigger CatchLogins on ALL Server
for CREATE_LOGIN
as
declare @data xml
set @data = eventdata()
SELECT @data

That doesn’t do much, but if I run this code:

CREATE LOGIN Delaney WITH PASSWORD = 'test'

I get this result:

Not overly helpful, but if you click on it, you see the event data as an XML document

<EVENT_INSTANCE>
  <EventType>CREATE_LOGIN</EventType>
  <PostTime>2012-07-23T11:47:41.427</PostTime>
  <SPID>62</SPID>
  <ServerName>SEVENFALLS</ServerName>
  <LoginName>SevenFalls\Steve</LoginName>
  <ObjectName>Delaney</ObjectName>
  <ObjectType>LOGIN</ObjectType>
  <DefaultLanguage>us_english</DefaultLanguage>
  <DefaultDatabase>master</DefaultDatabase>
  <LoginType>SQL Login</LoginType>
  <SID>Djwpf8IHNUicam9m2DkoBQ==</SID>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>CREATE LOGIN Delaney WITH PASSWORD = '******'
</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

I can parse this out and store it. What do I want? Probably I want the server and object, the date for tracking, maybe the creator, but definitely the SID. The text doesn’t help since it doesn’t have the password. All I can do then is go find the user or admin and ask them to recreate this login on the secondary servers.

Let’s start parsing. You have two choices here with the XML: the .data or .query methods. There may be more, but that’s what I know. I’ll parse in two ways here:

ALTER trigger CatchLogins on ALL Server
for CREATE_LOGIN
as
declare @data xml
set @data = eventdata()
select 
  @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
, @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(1000)')
, @data.query('(/EVENT_INSTANCE/ServerName)')

This returns some data.

You can see the .query returns XML, which (to me) is a hassle. So I’ll stick with the .value clause.

I would probably create a table here that stores this data. If I used a generic table for multiple types of audit data, I’d need to include the type of event as well. You can just use the first XML document for different audit types to see what’s returned, and then deal with it as appropriate.

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