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