mdDswYB1

1USE master;
2IF NOT EXISTS (SELECT 1 FROM sys.server_audits sa WHERE sa.name = 'AuditTest')
3BEGIN
4    CREATE SERVER AUDIT AuditTest 
5    TO FILE (FILEPATH = 'D:SQLServerMVAudits', MAXSIZE = 2MB, MAX_ROLLOVER_FILES = 1, RESERVE_DISK_SPACE = ON)
6    WITH (
7        QUEUE_DELAY = 0
8        , ON_FAILURE = CONTINUE
9        , AUDIT_GUID = N'B126B4DD-3973-4993-9ADF-4D324A15A8A4'
10    );
11END
12ALTER SERVER AUDIT AuditTest WITH (STATE = ON);
13GO
14	
15IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'AuditTest') 
16BEGIN
17    ALTER DATABASE AuditTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
18    DROP DATABASE AuditTest;
19END
20CREATE DATABASE AuditTest;
21ALTER DATABASE AuditTest SET RECOVERY SIMPLE;
22BACKUP DATABASE AuditTest TO DISK = 'NUL:';
23ALTER DATABASE AuditTest SET PARAMETERIZATION SIMPLE;
24GO
25	
26USE AuditTest;
27CREATE TABLE dbo.TestTable
28(
29    id int NOT NULL
30        CONSTRAINT FK_TestTable
31        PRIMARY KEY
32        CLUSTERED
33        IDENTITY(1,1)
34    , someVal varchar(30) NOT NULL
35);
36	
37CREATE DATABASE AUDIT SPECIFICATION AuditTestSpec
38FOR SERVER AUDIT AuditTest
39ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.TestTable BY dbo)
40WITH (STATE = ON);
41	
42CREATE PROCEDURE dbo.AuditTest
43(
44    @t varchar(50)
45)
46AS
47BEGIN
48    INSERT INTO dbo.TestTable (someVal) --stored procedure
49    VALUES (@t);
50END
51GO
52EXEC dbo.AuditTest @t = 'runtime value not audited - procedure';
53GO
54	
55DECLARE @t varchar(50);
56SET @t = 'runtime value not audited - direct insert';
57INSERT INTO dbo.TestTable (someVal) --direct insert
58VALUES (@t);
59
60SELECT *
61FROM dbo.TestTable tt
62WHERE tt.someVal = @t;
63	
64INSERT INTO dbo.TestTable (someVal)
65VALUES ('runtime value audited - direct stmt1');
66GO
67INSERT INTO dbo.TestTable (someVal)
68VALUES ('runtime value audited - direct stmt2');
69GO
70	
71USE master;
72DECLARE @stmt nvarchar(max);
73DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
74FOR
75SELECT af.statement
76FROM fn_get_audit_file('D:SQLServerMVAuditsAuditTest_B126B4DD-3973-4993-9ADF-4D324A15A8A4_*', default, default) af;
77OPEN cur;
78FETCH NEXT FROM cur INTO @stmt;
79WHILE @@FETCH_STATUS = 0
80BEGIN
81    PRINT @stmt;
82    FETCH NEXT FROM cur INTO @stmt;
83END
84CLOSE cur;
85DEALLOCATE cur;