At the risk of going slightly off topic, can I ask the gurus' opinions of DDL triggers?
In my infancy as accidental, lone DBA - we were undergoing a large period of change. External third parties were connecting in and "doing stuff" frequently. I wanted a method to track certain things that they had done.
Are they best avoided or did my rather naive script below have any merit? Would a DDL trigger for CREATE DATABASE be best avoided as a basis notifying/auditing the OP's situation?
CREATE TRIGGER [DDLAudit]
ON ALL SERVER
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, ALTER_PROCEDURE, ALTER_DATABASE
SET NOCOUNT ON;
@EventData XML = EVENTDATA();
@ip VARCHAR(32) =
WHERE session_id = @@SPID
INSERT INTO AuditDB.dbo.DDLEvents