Thursday, June 21, 2012

Auditing in SQL Server 2005 - DDL triggers

If you need to implement server/DDL auditing in SQL 2005, you have to either use notifications or DDL triggers. This note is just about DDL triggers.

On a database level, you can audit all database events with a DDL trigger:
CREATE TRIGGER AuditTrigger
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS  AS..


On a server level, if you attempt to create trigger for all server events:
CREATE TRIGGER AuditTrigger_Server
ON ALL SERVER  FOR DDL_SERVER_LEVEL_EVENTS AS...



you will get the following error:

"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration



That's for 2 reasons: DDL_DATABASE_EVENTS cannot be used in DDL trigger on server level and there is a bug if you use DDL_SERVER_SECURITY_EVENTS.

Workaround for both issues:
CREATE TRIGGER AuditTrigger_Server
ON ALL SERVER FOR DROP_DATABASE, CREATE_DATABASE, ALTER_DATABASE, DDL_login_events, DDL_gdr_server_events, DDL_authorization_server_events  AS...

Future versions:
SQL 2008 and 2012 have a new, easy way to implement auditing functionality using
CREATE SERVER/DATABASE AUDIT [SPECIFICATION]... commands

No comments: