SQL Server 2005 auditing using event notifications

  • Hello!

    I'm trying to implement SQL Server audit using service broker and event notifications.

    Along with usual audit info such as successful and failed logins, object alteration and permission change, i would also like to see when users try to access database objects which they have no access to.

    In TRC_SECURITY_AUDIT event group there are events

    AUDIT_DATABASE_OBJECT_ACCESS_EVENT and AUDIT_SCHEMA_OBJECT_ACCESS_EVENT that occur whenever an object is accessed. I was going to use these events together with analyzing field Success, and if it's unsuccessful, then log this activity.

    The problem is that audit procedure itself is generating these events by inserting audit info into audit table! Audit table is filling up with the same event - AUDIT_SCHEMA_OBJECT_ACCESS_EVENT - and server CPU usage is 100% constantly.

    If only it was possible to turn on these events in each database not server-wide, then audit table could reside in another database.

    I tried to look into SP_TRACE_CREATE / SP_TRACE_SETEVENT procedures to implement auditing but trace is server-wide too, so probably will generate same recursive events.

    Anyone has an idea how to avoid AUDIT_SCHEMA_OBJECT_ACCESS_EVENT recursive events for audit data insertion?

  • I'm sure it's faaaaaar too late to be useful, but I just came across this and figured I'd add my 2 cents. (note I didn't test it, and I'm curious what your solution wa)

    1) change your activation SP. Write to a different database. Tell your activation sp to ignore records going to the new DB and not message_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)' ) = 'yournewdb'

    2) change your activation SP. Add an account and run the activation SP under that context. Now, tell your activation SP to only insert when the loginname is not your new account.

    3) send to a secondary server. (Not necessarily very easy, but definitely doable)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply