September 26, 2011 at 12:55 pm
Okay so I created a server audit and then created a database specification that logs insert, delete, and update by public on a particular table. The audit log works fine, i.e. it captures all insert, delete and update statements for that table, however, it erroneously (at least erroneous for my purposes) logs select statements to sysobjects. For example there are frequent statements from one of the DB's user interfaces that runs the following query:
SELECT NAME, PERMISSIONS(OBJECT_ID(NAME)) as PERMISSION from sysobjects where type = 'U'
The audit log that I created records these statements as "Action ID" update, delete or insert for some reason even though the statement is clearly select AND it's a query to sysobjects not the table specified in the database audit specification that I created. Does anyone know what's going on here? Thanks.
September 27, 2011 at 10:15 am
It would be easier to help if you posted the Audit definition.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 27, 2011 at 2:43 pm
The audit is as follows:
USE master ;
GO
CREATE SERVER AUDIT DB1_DML
TO FILE ( FILEPATH =
'D:\AuditLog\DB1DML' ) ;
GO
ALTER SERVER AUDIT DB1_DML
WITH (STATE = ON) ;
GO
USE DB1;
GO
CREATE DATABASE AUDIT SPECIFICATION DB1DML_Audit_Spec
FOR SERVER AUDIT DB1_DML
ADD (UPDATE , INSERT, DELETE
ON DB1.dbo.TB1 BY public )
WITH (STATE = ON) ;
GO
And again this works exactly the way I want it to except that it also logs select statements to sysobjects. My guess is that this is a security feature of the auditing that is supposed to gaurd against attempts to circumvent the audit so that the events leading up to a successful circumvention are logged before the perpetrator acutals gets around the audit. However, in my case there are so many calls to sysobjects that the audit log bloats very quickly and I'm not much better off that when I was using C2 audit tracing which was the whole reason that I tried to implement SQL 2008's new audit logging.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply