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

Question

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?

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.

• Erik Reasonable Rates Darling: 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. -- That's one of the most magnificent side effects of scalar UDFs I've ever heard, and I've heard a lot.
• Scott Hodgin - Retired: Is there an option of creating UDF's you don't want audited in a separate database (that isn't audited) and invoking them via 3-part-name?
• Mark Iannucci: @ScottHodgin, I like the workaround, but in our circumstances there are some Scalar Valued Functions that are delivered by a vendor which we cannot delete or move to an alternate database.
• Mark Iannucci: Those following along may be wondering what case causes the query to write to the audit log for every row in a result set; we've noticed it occurring when the scalar function is in use in a recursive CTE.

Answer 1

There are a few options that I was able to get working. All of the options deal with variations of filter predicates. NOTE: you must disable the Server Audit in order to make changes, and then re-enable it.

First, the most generic approach is to filter out all Scalar UDFs. You can do that by using the class_type audit field. The documentation indicates that this field is VARCHAR(2), but it doesn't allow specifying a string. However, I did get the following to work:

ALTER SERVER AUDIT [servAudit]
WHERE ([class_type] <> 20038); -- EXECUTE Scalar UDF

(more info on that investigation here: Server Audit Mystery: Filtering class_type gets Error Msg 25713)

The next most generic approach is not an option since it was stated that this is a vendor-supplied database and hence no changes can be made. So I will cover that last.

The least generic approach (but one that definitely works) is to filter out the specific function name:

ALTER SERVER AUDIT [servAudit]
WHERE ([object_name]<>'function_name');

Or, if multiple names:

ALTER SERVER AUDIT [servAudit]
WHERE ([object_name]<>'function_name1' AND [object_name]<>'function_name2');

While not very generic, this approach should be fine since the number of functions to filter out should be fairly small, and it won't be very often that new functions are introduced.

Finally, to others who face this situation and are not restricted from making changes: you can place functions into their own Schema and then filter out just that Schema. This is more generic than filtering out the functions individually. Assuming that you create a Schema named fn and place the function(s) into it:

ALTER SERVER AUDIT [servAudit]
WHERE ([schema_name]<>'fn');

---

ALSO, regarding the following two comments in the question:

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).

and:

We would like to avoid writing a stored proc which hard codes the object_id in the WHERE clause

The IN operator isn't the issue. True, it is not supported, but it's just shorthand for a list of OR conditions. The actual issue is the use of T-SQL. Only literals — strings or numbers — are allowed. So you wouldn't have been able to execute a Stored Procedure anyway. Nor can you use the built-in functions.

• Mark Iannucci: thank you for this response. We are in the process of implementing this change, and I will accept this answer when we confirm it works in our environment.
• Solomon Rutzky: @MarkIannucci Thanks! Also, I just fixed a minor bug in my ideal suggestion. I had copied and pasted from testing where I was filtering FOR functions instead of ANYTHING BUT functions. I changed the = to be <> in my answer. I also just tested it and it works as advertised :-)