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 anonymous user,

Thank you so much for posting here.

You could try with dynamic SQL statement as below:

BEGIN  
  
DECLARE @SQL NVARCHAR(MAX)  
  
DECLARE @Id INT = 6;  
  
DECLARE @Val Varchar(10) ='UPDATE NOW';  
  
SET @SQL= 'DELETE TESTTABLE WHERE id ='+ CONVERT(VARCHAR(10),@Id)+';'  
  
EXECUTE sp_executesql  @SQL  
  
set @ID = 5;  
  
SET @SQL= 'UPDATE TESTTABLE set NAME ='''+@Val+''' WHERE id = '+CONVERT(VARCHAR(10),@Id)+';'  
  
EXECUTE sp_executesql  @SQL  
  
END  

Then you could get your expected value instead of variable.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table