SQL Server Database Audit specification UPDATE, DELETE operations, when we pass variables in the where clause, audit logs variable as is instead of value

Question

I have created SQL Server Database Audit specification as below, audit is working perfectly fine but INSERT, UPDATE, DELETE operations in the database when we pass variables in the where clause, audit logs variable as is instead of value.

CREATE DATABASE AUDITSPECIFICATION [DatabaseAuditSpecification]

FOR SERVER AUDIT[Audit_001]

ADD (SELECT ON DATABASE::[XXX] BY [dbo]),

ADD (INSERT ON DATABASE::[XXX] BY [dbo]),

ADD (UPDATE ON DATABASE::[XXX] BY [dbo]),

ADD (EXECUTE ON DATABASE::[XXX] BY [dbo]),

ADD (DELETE ON DATABASE::[XXX] BY [dbo]) 

WITH (STATE

ON)

When I run the following T-SQL code in SQL Server, the audit file shows variables @Id , @Val that I declared in the code , instead of the values assigned in the code.

BEGIN 

DECLARE @Id INT = 6;

DECLARE @Val Varchar(10) ='UPDATE NOW';

DELETE testTable WHERE col1 = @Id;

set @ID = 5;

UPDATE testTable set col2 =@Val WHERE col1 = @Id;

END

I used fn_get_audit_file to see the audit log, the below is the output of audit logs with variables instead of values passed in the code.

SELECT aud.action_id, aud.object_name AS [Object], aud.statement

FROM fn_get_audit_file('C:\Audit_XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX0000.sqlaudit',DEFAULT, DEFAULT) aud

where aud.action_id in ('UP','DL','IN') order by event_time desc

action_id Object statement

DL  testTable DELETE testTable WHERE col1 = @Id

UP  testTable UPDATE testTable set col2 =@Val WHERE col1 = @Id

IN  testTable INSERT INTO testTable  VALUES('Insert Test2')

DL  testTable DELETE testTable WHERE col1 = @Id

Please kindly help if you had came across this issue and fixed it.

Thanks,

Siva

Answer 1

Hi Siva Kumar,

Thanks for choosing Microsoft and sharing your experience in our community.

Since your issue is related to SQL Server Database Audit specification, while such issue is beyond our support boundary, I’ve also searched a lot, seems we have limited resources on it, so to help you troubleshoot it better and resolve it ASAP, we suggest you post the question in our SQL Server forumfor the dedicated help. This is the specific channel which handles SQL server related question and queries, and the experts there will further investigate it and provide some professional suggestions for you.

Appreciate your understanding and cooperation.😊

Have a nice day and stay safe!

Best regards,

Gloria

Answer 2

Thank you Gloria Li, as suggested I have posted in SQL Server Forum https://docs.microsoft.com/en-us/answers/questions/194410/sql-server-database-audit-specification-update-del.html