4

I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The statement shows the parameter name rather then the value i.e. WHERE CaseId=@P_CaseId rather than WHERE CaseId =100

Now I know there was a hotfix for SQL 2008 (not R2) that made this happen: http://support.microsoft.com/kb/967552.

Did this not get rolled into R2? Is there a different fix I can apply? Is there a workaround anyone knows?

3
  • Just checking, but did you read the More Information section of that article? The fix only applies to certain situations (most, but not all).
    – Jon Seigel
    Sep 14, 2012 at 16:49
  • I've tried both parameterization settings - but I'm on R2 where the behaviour seems to be the same, but there's no explicit fix for R2 (that I can see) so I'm confused. Was it not changed in R2?
    – Rikalous
    Sep 18, 2012 at 10:53
  • I assume the fixed behaviour in 2008 would carry over to R2. I was just checking to make sure that none of the exceptions apply to your situation.
    – Jon Seigel
    Sep 18, 2012 at 15:45

1 Answer 1

2

I can confirm in SQL Server 2008 R2 Service Pack 3 (v10.50.6000.34), simple parameterized queries do result in the parameterized values being recorded in the audit log.

I setup a simple test rig, as follows.

Create a server audit:

USE master;
IF NOT EXISTS (SELECT 1 FROM sys.server_audits sa WHERE sa.name = 'AuditTest')
BEGIN
    CREATE SERVER AUDIT AuditTest 
    TO FILE (FILEPATH = 'D:\SQLServer\MV\Audits', MAXSIZE = 2MB, MAX_ROLLOVER_FILES = 1, RESERVE_DISK_SPACE = ON)
    WITH (
        QUEUE_DELAY = 0
        , ON_FAILURE = CONTINUE
        , AUDIT_GUID = N'B126B4DD-3973-4993-9ADF-4D324A15A8A4'
    );
END
ALTER SERVER AUDIT AuditTest WITH (STATE = ON);
GO

Create a database where we can play with auditing:

IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = N'AuditTest') 
BEGIN
    ALTER DATABASE AuditTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE AuditTest;
END
CREATE DATABASE AuditTest;
ALTER DATABASE AuditTest SET RECOVERY SIMPLE;
BACKUP DATABASE AuditTest TO DISK = 'NUL:';
ALTER DATABASE AuditTest SET PARAMETERIZATION SIMPLE;
GO

Create a table in the database:

USE AuditTest;
CREATE TABLE dbo.TestTable
(
    id int NOT NULL
        CONSTRAINT FK_TestTable
        PRIMARY KEY
        CLUSTERED
        IDENTITY(1,1)
    , someVal varchar(30) NOT NULL
);

Create a database audit spec:

CREATE DATABASE AUDIT SPECIFICATION AuditTestSpec
FOR SERVER AUDIT AuditTest
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.TestTable BY dbo)
WITH (STATE = ON);

Create a stored procedure and trigger an audit action:

CREATE PROCEDURE dbo.AuditTest
(
    @t varchar(50)
)
AS
BEGIN
    INSERT INTO dbo.TestTable (someVal) --stored procedure
    VALUES (@t);
END
GO
EXEC dbo.AuditTest @t = 'runtime value not audited - procedure';
GO

Perform a direct parameterized test:

DECLARE @t varchar(50);
SET @t = 'runtime value not audited - direct insert';
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);

SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;

Finally, run a simple-parameterization test:

INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
GO
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');
GO

The execution plan for the above two simple-parameterized statements shows simple parameterization is working as expected:

enter image description here

Read the audit log, using a cursor and PRINT to preserve formatting:

USE master;
DECLARE @stmt nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT af.statement
FROM fn_get_audit_file('D:\SQLServer\MV\Audits\AuditTest_B126B4DD-3973-4993-9ADF-4D324A15A8A4_*', default, default) af;
OPEN cur;
FETCH NEXT FROM cur INTO @stmt;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @stmt;
    FETCH NEXT FROM cur INTO @stmt;
END
CLOSE cur;
DEALLOCATE cur;

Results:

INSERT INTO dbo.TestTable (someVal) --stored procedure
    VALUES (@t);
INSERT INTO dbo.TestTable (someVal) --direct insert
VALUES (@t);

SELECT *
FROM dbo.TestTable tt
WHERE tt.someVal = @t;

INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt1');
INSERT INTO dbo.TestTable (someVal)
VALUES ('runtime value audited - direct stmt2');

As you can see in the above output, the simple-parameterized statement run-time values were captured in the audit log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.