Suggestion - Update to SchemaAuditDDLTrigger that would disable rebuild for non-audited tables.

Sep 18, 2015 at 3:56 PM
Edited May 4, 2016 at 10:28 PM
I've added AutoAudit to my project. I only have a few key tables that I need audited. However, we have multiple folks working in the database. After AutoAudit is installed every user that issues a DROP and ALTER DDL statement against the database is greeted by an informational message from AutoAudit.
Dropping AutoAudit components from table: [dbo].[MyTable]
    Dropping Table Audit DDL
    Dropping Table Audit Triggers
    Dropping Table Audit Views
 
I think this message is useful when DROPing and ALTERing audited table, but could be confusing for developers DROPing and ALTERing non-audited tables. What is this? Why are we receiving this message?

The message is caused by the SchemaAuditDDLTrigger which will call pAutoAuditRebuild or pAutoAuditDrop when it detects DML containing either an ALTER or DROP statement respectively. That automatic triggering of the drop or rebuild SPs is VERY useful when working with audited tables, but its just noise when dropping a non-audited table.

For a fix, I can see that there is an AuditSetting called
"Launch pAutoAuditRebuild from SchemaAuditDDLTrigger Enabled Flag"
Setting this to zero would disable ALL of the automatic rebuild goodness. But I still want that! I just need the information messages squashed for other tables. My thought is that we could update the SchemaAuditDDLTrigger to perhaps check the AuditBaseTable for the existence of an audited object before automatically triggering the SPs? Maybe there is a better way to detect that a table is being audited? Good idea? Alternatives?

This seems related to Issue 35828
https://autoaudit.codeplex.com/workitem/35828

Thanks,
  • Troy Witthoeft
May 4, 2016 at 10:42 PM
Edited May 4, 2016 at 10:46 PM
Here is the updated Database SchemaAuditDDLTrigger I am using. Working great so far. Maybe make this a setting?
Updates on lines 57 and 63. These additions make the following update. When calling SchemaAuditDDLTrigger only include audited tables from for the triggering of pAutoAuditDrop and pAutoAuditRebuild.
/****** Object:  DdlTrigger [SchemaAuditDDLTrigger]    Script Date: 05/04/2016 18:32:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--Note: Database triggers must be created in the dbo schema   
CREATE TRIGGER [SchemaAuditDDLTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS 
BEGIN

  -- generated by AutoAudit Version 3.30a on Mar 14 2016 11:16AM
-- www.SQLServerBible.com 
  -- Paul Nielsen and John Sigouin
  SET NoCount ON
  SET ARITHABORT ON
  SET ANSI_PADDING ON
  
declare @ContextInfo varbinary(128)
select @ContextInfo = context_info from master.dbo.sysprocesses where spid=@@SPID;

--check for recursive execution  of trigger 
IF @ContextInfo = 0x1
    RETURN 

  If 0 = isnull((SELECT [SettingValue] from [Audit].[AuditSettings] where [SettingName] = 'SchemaAuditDDLTrigger Enabled Flag'),1)
    RETURN --The database DDL trigger configuration is set to disabled

  DECLARE 
    @EventData XML,
    @Schema sysname,
    @Object sysname,
    @EventType sysname,
    @SQL VARCHAR(max)
    
  SET @EventData = EventData()
  
  SET @Schema = @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'VARCHAR(50)')
  SET @Object = @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(50)')
  SET @EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(50)')
  
  INSERT [Audit].SchemaAudit (AuditDate, LoginName, UserName, [Event], [Schema], Object, TSQL, [XMLEventData])
  SELECT 
    GetDate(),
    SUSER_SNAME(),
    @EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'sysname'),
    @EventType, @Schema, @Object,
    @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)'),
    @EventData
    
  IF 1 = isnull((SELECT [SettingValue] from [Audit].[AuditSettings] where [SettingName] = 'Launch pAutoAuditRebuild from SchemaAuditDDLTrigger Enabled Flag'),1)
  IF @EventType = 'ALTER_TABLE'
    IF @Object IN (SELECT TableName FROM [Audit].[AuditBaseTables])
        BEGIN 
          SET @SQL = 'EXEC [Audit].pAutoAuditRebuild @SchemaName = ''' + @Schema + ''', @TableName = ''' + @Object + ''''
          EXEC (@SQL)
        END    
  IF @EventType = 'DROP_TABLE'
    IF @Object IN (SELECT TableName FROM [Audit].[AuditBaseTables])
        BEGIN 
          SET @SQL = 'EXEC [Audit].pAutoAuditDrop @SchemaName = ''' + @Schema + ''', @TableName = ''' + @Object + ''''
          EXEC (@SQL)
        END    
END   



GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [SchemaAuditDDLTrigger] ON DATABASE
GO

ENABLE TRIGGER [SchemaAuditDDLTrigger] ON DATABASE
GO