Auditing Oddities – Why Would a Select Query Cause An Insert/Update/Delete Audit Record To Be Generated?

Quite the interesting problem was brought to me by a friend, in that they had a database level audit setup and were having `select` queries trigger audits that the object had updates run on them. The question became, how is this possible as a select query doesn’t update anything. Let’s start with a quick setup and original output.

USE [master]
GO

CREATE SERVER AUDIT [OddAudit]
TO FILE 
(	FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
ALTER SERVER AUDIT [OddAudit] WITH (STATE = ON)
GO

USE [AuditMe]
GO

CREATE DATABASE AUDIT SPECIFICATION [MyDBAudit]
FOR SERVER AUDIT [OddAudit]
ADD (DELETE ON OBJECT::[dbo].[EmployeeSalaries] BY [public]),
ADD (INSERT ON OBJECT::[dbo].[EmployeeSalaries] BY [public]),
ADD (UPDATE ON OBJECT::[dbo].[EmployeeSalaries] BY [public])
WITH (STATE = ON)
GO

Using the above, let’s run the following query and check our audit output.

SELECT * FROM EmployeeSalaryAccess

That seems… unexpected. What’s going on here?! I left out the object definition on purpose, though you’ll probably wonder why it works the way it does. Taking a look at the object definition, it utilizes an intrinsic security function HAS_PERMS_BY_NAME.

CREATE TABLE EmployeeSalaraies
(
EmployeeID	INT NOT NULL,
Salary		DECIMAL(16, 2) NOT NULL
);
GO

CREATE VIEW EmployeeSalaryAccess
AS
SELECT HAS_PERMS_BY_NAME('dbo.EmployeeSalaries', 'OBJECT', 'INSERT') as HasAccess
GO

This built-in security function has the side effect of logging an audit result, if auditing for the permission is enabled, regardless of if the action was actually taken. This seems logical, you’d want to know if someone was checking their permissions against an audited object to see if they can mess around with it. Where it may not be so obvious is that this function is rarely used in developer written T-SQL modules or queries. It is, however, used in system views and stored procedures (such as sys.sp_columns_100_rowset) which drivers tend to use extensively, such as running linked server queries to another instance of SQL Server.

So, yeah, that’s how many of the security intrinsic functions work which isn’t extremely intuitive coupled with potential layers of indirection such as drivers running their own queries to get metadata.