SQL Server 2016 Audit Actions, Can DatabaseAuditSpecification capture param values used in statement?

Question

I am using Schema_Object_access_Group audit action type. When I run a select, insert, update SQL statement, it records all statements nicely.

For example, captured Sql statement looks like :

UPDATE TOP (200) SomeTable 
SET ColName1 = @ColName1
WHERE (ColName2 = @ColName2) AND ...

It is not explaining what was updated value and which row was updated.

I want to capture updated actual value used in SET ColName1 = @ColName1, is there an audit action type to accomplish the job. I want a way to capture parameters used in any select, update, insert statements.

Willing to use audit events as change log, to track who changed what without using the following:

1. Trigger approach to log all changed values
2. Change Data Capture "CDC"

Answer 1

There is no audit action to capture this information, it is a limitation of SQL Server Auditing. Basically it captures the statements after the query optimiser has already parameterised the query.

There are two open requests that I know of to alter this behaviour (here and here) that you could vote for, however, this limitation has been around for some time and doesn't seem likely to change. Note that this is apparently different in Azure SQL Database, however, I haven't tested this to confirm.

Your options for this are triggers, CDC, SQL Trace or third-party auditing tools.