SchemaAuditDDLTrigger not pulling in user

Mar 14, 2012 at 4:46 PM

Hi I had an issue that the schema tracking wasn tpulling in the correct username in my SQL 2008 R2 Standard

I changed the line in the "CREATE PROCEDURE [SchemaAuditDDLTrigger]"

from

@EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME')

to

SYSTEM_USER,--@EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME')

So the complete insert looks like this now

INSERT SchemaAudit (AuditDate, UserName, [Event], [Schema], Object, TSQL, [XMLEventData])
  SELECT 
    GetDate(),
    SYSTEM_USER,--@EventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME')
    @EventType, @Schema, @Object,
    @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(max)'),
    @EventData

I left the original code in there just in case others want to revert