Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SQL Server 2005 auditing using event notifications Expand / Collapse
Posted Thursday, June 4, 2009 12:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 23, 2011 12:50 AM
Points: 1, Visits: 55

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?
Post #728684
Posted Tuesday, September 11, 2012 3:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 24, 2016 10:40 PM
Points: 33, Visits: 623
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)

Post #1357721
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse