SQL Server Auditing - Example

Set up objects

select * from master.dbo.ddl_all_databases

create table master.dbo.ddl_all_databases
( DateTime^? datetime,
LoginName^? char(20),
DBName char(20),
UserName^? char(20),
EventType^? char(200),
CommandText^? char(1000))

CREATE TRIGGER Trig_DBEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS INSERT master.dbo.ddl_all_databases
(
DateTime^?,
LoginName^?,
DBName,
UserName^?,
EventType^?,
CommandText^?
)
SELECT GETDATE(),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(1024)')
GO

Monitored events

This will monitor:

• DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, DDL_SSB_EVENTS, DDL_DATABASE_SECURITY_EVENTS, DDL_EVENT_NOTIFICATION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TYPE_EVENTS, DDL_XML_SCHEMA_COLLECTION_EVENTS, DDL_PARTITION_EVENTS, DDL_ASSEMBLY_EVENTS

Preferably you can start monitoring with only:

• DDL_USER_EVENTS,DDL_INDEX_EVENTS,DDL_TABLE_EVENTS

Trigger on the SERVER level can be set up and monitor DDL_SERVER_LEVEL_EVENTS instead (ALTER DB, adding, dropping roles,etc...)