Sql-server – An INSERT statement triggers the SQL Server Audit to write it down in the logs although INSERT is not listed as an Audit Action

auditlogssql serversql-server-2019

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)

Best Answer

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

Related Question