Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56

    Unanswered: audit trigger error

    I'm setting up trigger-based DDL audits on MSSQL 2005.

    Going fine so far, CREATE TABLES, etc works great.
    Up to 40, no problem

    However, I get to a event requested by the security scanning app we're using.
    Object Created
    And I get this error...

    "OBJECT_CREATED" does not support synchronous trigger registration.

    Now, there is no documentation stating specifically what this means,
    but some investigation indicates that this event may be incompatible with 2005
    or it can only be utilized with Notification services.
    Nothing firm though...

    I'd like to just add the events the app is requesting to get it to stop "failing" the criteria.

    Anybody know specifically what may be the issue?
    Thanks for any input.

    Below is a sample of the trigger code:
    ----------------
    CREATE TRIGGER audit_object_created
    ON DATABASE
    FOR object_created
    AS
    SET ANSI_PADDING ON
    DECLARE @data XML
    SET @data = EVENTDATA()
    INSERT Audit.dbo.Audit
    (
    PostTime,
    StartTime,
    EventType,
    SPID,
    LoginName,
    LoginSid,
    ServerName,
    DatabaseName,
    NTUserName,
    NTDomainName,
    HostName,
    ApplicationName,
    SchemaName,
    ObjectType,
    ObjectName,
    ObjectID,
    ObjectID2,
    DatabaseID,
    TransactionID,
    ClientProcessID,
    IndexID,
    RequestID,
    GroupID,
    EventSubClass,
    IntegerData,
    XactSequence,
    EventSequence,
    BigintData1,
    IsSystem,
    SessionLoginName,
    TSQLCommand)
    VALUES
    (
    @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/StartTime)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/LoginSid)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/NTUserName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/HostName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ObjectID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ObjectID2)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/TransactionID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ClientProcessID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/IndexID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/RequestID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/GroupID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/EventSubClass)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/IntegerData)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/XactSequence)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/EventSequence)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/BigintData1)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/IsSystem)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
    ) ;
    GO

  2. #2
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    Nevermind, I've worked it out.
    This one (and some others) needs to be setup for Event Notification Services.

    The ones I've done already are strictly for DDL triggers.

    Now I just have to work that out...

    Thanks for looking

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Strictly speaking, since your intent is auditing, then Event Notifications are more suited are they not? I mean, DDL triggers have the opportunity to roll back events as well, and as such they will cause locks to be held longer. This is not the case with Event Notifications.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •