Sunday, February 22, 2009

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...)

2 comments:

Jhon Drake said...
This comment has been removed by the author.
james marsh said...

Great command for auditing sql server, I tried the automated tool from https://www.netwrix.com/sql_server_auditing.html which enable sql server audit feature to audit all sql server and track every changes to know what happening at the database and generates the reports and instant time alerts as per auditing requirement.