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

    Unanswered: all database auditing

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

    I've found documentation that states the certain DDL can be audited either ON SERVER or ON DATABASE.

    I'd like to create triggers that audit no matter which database is on the instance,
    however, when attempting to create a CREATE_TABLE audit trigger ON SERVER, it says this is not allowed.
    It does work ON DATABASE, but the documentation says for this statement both are available.

    Is the documentation wrong or am I?
    I'd rather not need to create one for each and every database, while scripted it just adds to the total...

    Sample code:
    CREATE TRIGGER audit_create_table
    ON DATABASE
    FOR create_table
    AS
    SET ANSI_PADDING ON
    DECLARE @data XML
    SET @data = EVENTDATA()
    INSERT Audit.dbo.tbl_DDL_audit
    (ProgramName,
    EventTime,
    UserName,
    LoginName,
    EventType,
    ServerName,
    SPID,
    DatabaseName,
    ObjectType,
    ObjectName,
    TSQLCommand)
    VALUES
    (
    program_name(),
    GETDATE(),
    suser_sname() ,
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'),
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
    ) ;

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    CREATE_TABLE is supported on database only, not on server:

    Event Groups for Use with DDL Triggers
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Oct 2003
    Location
    Near D.C.
    Posts
    56
    Thank you for the clearing that up, looks like the documentation I had was incorrect.

    Oh well, looks like I'll be starting up lots of triggers...

Posting Permissions

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