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
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?
We've noticed that when the scalar function is in use in a recursive CTE, then it causes the query to write to the audit log for every row in the result set.
There are some Scalar Valued Functions that are delivered by a vendor which we cannot delete or move to an alternate database.