SQL DDL Audit Log Trigger

SQL DDL Audit Log Trigger

-- Audit Log Script
USE Configuration
GO

--All connections to the database must be cleared first before enable broker can complete 
IF(SELECT is_broker_enabled FROM sys.databases WHERE name = 'Configuration') = 0
ALTER DATADDLAudit SQL 2008BASE Configuration SET ENABLE_BROKER;
GO


GRANT SEND ON SERVICE::[AuditDDLService] TO PUBLIC
GO

DECLARE @processorcount int
DECLARE @sql varchar(max)
DECLARE @maxreaders int

SELECT @processorcount = MAX(cpu_id) + 1
FROM sys.dm_os_schedulers


SELECT @maxreaders = CASE WHEN @processorcount <=2 THEN 1
WHEN @processorcount between 3 and 4 THEN 2
WHEN @processorcount >4 THEN 4 
ELSE 1 END

SET @sql = '
ALTER QUEUE dbo.DDLQueue WITH STATUS = ON, 
ACTIVATION(STATUS=ON,PROCEDURE_NAME = dbo.ReadDDLQueue, 
MAX_QUEUE_READERS=' + cast(@maxreaders as varchar(10)) + N',EXECUTE AS SELF)
'
EXEC(@sql)
GO

DECLARE @sql varchar(max)
DECLARE @SBGuid uniqueidentifier;
SELECT @SBGuid = service_broker_guid
FROM sys.databases
WHERE name = 'Configuration'
--PRINT @SBGuid

SET @sql = '
CREATE EVENT NOTIFICATION [DDLAudit] ON SERVER FOR DDL_EVENTS,ALTER_SERVER_CONFIGURATION TO SERVICE ''AuditDDLService'' ,''' + CAST(@SBGuid as varchar(40)) + ''';
'

PRINT @sql
EXEC(@sql)
GO

--DROP EVENT NOTIFICATION [DDLAudit] ON SERVER;

Download Script File

Leave a Reply