SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 auditing using event notifications


SQL Server 2005 auditing using event notifications

Author
Message
thrn
thrn
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 55
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?
michael bourgon
michael bourgon
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 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)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search