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

To change the default backup folder on SQL cluster

• Use SQL Mgmt Studio 2008.
• Right click on server/facets
• Change BackupDirectory to the new value
  Click OK