edit: DDL Trigger: database level events

links

history

item nametags

==introduction
It can be desirable to monitor all DB `create`/`alter`/`drop` statements, on triggers, users, tables, etc.  This trigger, combined with [[DDL Trigger rename]], would provide _very_ nice auditing of database objects.  Unfortunately, `rename` only works in SQL2008.

==table definition
I haven't figured out which of these should never be null, which is why `not null` cannot be found below.
{{
create table DDL_Audit_Log (
	ddl_audit_log_pk  int identity primary key,
	object_name       varchar(64),
	object_id         int,
	object_type       varchar(40),
	post_time         datetime,
	login_name        varchar(30),
	host_name         varchar(64),
	ip_address        varchar(15),
	command_text      varchar(max),
	xml_data          xml,
)
}}

==trigger
{{
-- see for event types: http://msdn.microsoft.com/en-us/library/bb510452.aspx
-- WARNING: must be recompiled by someone with access to the system tables referenced;
--   it would probably be best to recompile this as sa
create trigger DDL_Audit on database 
with execute as self
for DDL_DATABASE_LEVEL_EVENTS
as

declare @xml_data xml
declare @command_text nvarchar(max)
declare @object_type varchar(40)
declare @object_name varchar(128)

set @xml_data = eventdata()
set @command_text = @xml_data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)')
set @object_type  = @xml_data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(40)')
set @object_name  = @xml_data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(64)')

insert	DDL_Audit_Log(object_name, object_id, object_type, post_time, login_name, 
                      host_name, ip_address, command_text, xml_data)
select	@object_name,
		object_id(@object_name),
		@object_type,
		@xml_data.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(24)'),
		@xml_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(30)'),
		host_name,
		client_net_address,
		@command_text,
		@xml_data
from	sys.dm_exec_sessions s
inner join sys.dm_exec_connections c on c.session_id = s.session_id
where	s.session_id = @xml_data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')
	and	(
				client_net_address != '<local machine>' 
			or	@object_type not in ('INDEX', 'STATISTICS')
			or	@object_type = 'INDEX' and @command_text not like 'ALTER INDEX%REBUILD%'
			or	@object_type = 'STATISTICS' and @command_text not like 'UPDATE STATISTICS%'
		)
go
}}

==helpful `select` statement
The `left(command_text, 200)` is nice for those working over slow connections.
{{
select	ddl_audit_log_pk,
		object_name,
		object_type,
		post_time,
		login_name,
		host_name,
		ip_address,
		left(command_text, 200)
from	DDL_Audit_Log
order by ddl_audit_log_pk
}}

some permissive license goes here
contact

tsunami