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?