1

Background: I am trying to keep a track record of what users are querying in the database. One option I found is to create a Server and Database Audit Specification. I did not want to include INSERT statements since those are going to be logged for each record inserted which will be a lot if there are bulks of data being added to the table(s).

Problem: We have a SQL Job (within the Job Agent) that inserts data into a table using an INSERT query. I would expect these logs would not appear in our .sqlaudit file but it does. Even do INSERT is not apart of our Actions within our Database Audit Specifications. I am wondering if it is triggered do to another action? I have listed all the actions that I have included but from my understanding none of them will trigger the server to write the INSERT logs in the .sqlaudit file. What could be the issue?

NOTE: I have also executed an INSERT query using the query tool and it still logs that as well.

Audit Action Types Added to Database Audit Specifications:

  • DATABASE_OBJECT_ACCESS_GROUP
  • SCHEMA_OBJECT_ACCESS_GROUP
  • DATABASE_OBJECT_CHANGE_GROUP
  • SCHEMA_OBJECT_CHANGE_GROUP
  • DELETE
  • EXECUTE
  • SELECT
  • UPDATE

Here is the statement that it is logging in the .sqlaudit file:

INSERT [dbo].[table_name]([a],[b],[c],[d],[e],[f],[g],[h],[i],[j],[k],[l]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011,@Param000012,@Param000013,@Param000014,@Param000015)
3
  • Is there any trigger configured on that table? When you run an INSERT outside of that job, is it also captured by the audit?
    – Ronaldo
    Mar 12, 2021 at 21:06
  • Is this insert part of a procedure? Do you EXECUTE it?
    – MBuschi
    Mar 13, 2021 at 17:26
  • @Ronaldo Yes, when I run the INSERT statement outside the job. It still logs the database.
    – Dip
    Mar 15, 2021 at 19:59

1 Answer 1

0

The optimal approach would be to disable the INSERT audit for that table, but since you seem to have a peculiar situation, here's one way of dealing with it:

Once you made sure the INSERT audit is not enabled by connecting to the audited database and running this query:

-- List enabled database specifications
SELECT  a.audit_id,
        a.name as audit_name,
        s.name as database_specification_name,
        d.audit_action_name,
        s.is_state_enabled,
        d.is_group,
        s.create_date,
        s.modify_date,
        d.audited_result
FROM sys.server_audits AS a
JOIN sys.database_audit_specifications AS s
ON a.audit_guid = s.audit_guid
JOIN sys.database_audit_specification_details AS d
ON s.database_specification_id = d.database_specification_id
WHERE s.is_state_enabled = 1

You can filter that audit to avoid the inserts from being registered. The Create a Server Audit and Server Audit Specification doc says:

  1. Optionally, on the Filter page, enter a predicate, or WHERE clause, to the server audit to specify additional options not available from the General page. Enclose the predicate in parentheses; for example: (object_name = 'EmployeesTable').

The Filter page can be found by right click the audit > properties. If you prefer, the T-SQL to alter the audit not to register the inserts would be something like this:

USE [master]
GO

ALTER SERVER AUDIT [Your_Audit_Name]
WHERE (NOT [statement] LIKE 'INSERT%')
GO

Some relevant content:

Filter SQL Server Audit on action_id / class_type predicate

sys.fn_get_audit_file (Transact-SQL)

How to analyze and read SQL Server Audit information

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.