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?