How to filter out Scalar Valued Function usage from SQL Server Audit Data?

by Mark Iannucci   Last Updated January 11, 2018 19:06 PM

We have a SQL Server database which has a database audit specification which audits all execute actions on the database.

CREATE DATABASE AUDIT SPECIFICATION [dbAudit]
FOR SERVER AUDIT [servAudit]
ADD (EXECUTE ON DATABASE::[DatabaseName] BY [public])

We've found that some queries will write to the audit log the use of a scalar function for every row in a result set. When this happens, the log fills up before we can ETL it into it's final resting place and we have a gap in our logging.

Unfortunately due to compliance reasons, we cannot simply stop auditing every EXECUTE statement.

Our first thought for approach to this problem is to use WHERE clause on the Server Audit to filter out the activity. The code looked like this:

WHERE [object_id] not in (Select object_id from sys.objects where type = 'FN' )

Unfortunately, SQL Server doesn't allow relational IN operator (probably because it doesn't want to query every time it has to write to the audit log).

We would like to avoid writing a stored proc which hard codes the object_id in the WHERE clause, but that is our current thinking on the best way to approach this problem. Is there an alternate approach that we should consider?

Tags : sql-server audit


Related Questions



View SQL Server 2016 Login Audits

Updated September 20, 2017 14:06 PM


Exclude a User From an Audit

Updated April 27, 2016 09:02 AM

SQL Server - storing audit to table

Updated October 06, 2016 09:02 AM