Home Dashboard Directory Help
Search

SSDT Server Audit object does not recognise LIKE operator in the WHERE predicate by Kent D Chenery


Status: 

Closed
 as Won't Fix Help for as Won't Fix


1
0
Sign in
to vote
Type: Bug
ID: 780003
Opened: 2/25/2013 8:32:43 PM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description


Creating a SQL Server Audit in SSDT object with a WHERE predicate does not recognise the LIKE operator.

The same statement can be manually run successfully on an instance.

A work around is to add the WHERE predicate as a post deploy script with a dynamic statement using sp_executesql prior to starting the audit.

This is using the December 2012 version of SSDT.
Details
Sign in to post a comment.
Posted by Microsoft on 7/26/2013 at 2:14 PM
Hello Kent. Thanks for the feedback. We triaged this issue and at this time do not plan to address this in a future version of SQL Server. The suggestion will be saved for consideration with future releases.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Sign in to post a workaround.
Posted by Kent D Chenery on 2/26/2013 at 2:11 AM
In the "Steps to reproduce" I gave the following example:

CREATE SERVER AUDIT Example
TO FILE (
    FILEPATH = 'C:\SQLAuditExample'
    ,MAXSIZE = 10 MB
    ,MAX_ROLLOVER_FILES = 10
    ,RESERVE_DISK_SPACE = OFF
    )
WHERE
    [statement] LIKE 'TRUNCATE TABLE%'


This could be worked around with:

CREATE SERVER AUDIT Example
TO FILE (
    FILEPATH = 'C:\SQLAuditExample'
    ,MAXSIZE = 10 MB
    ,MAX_ROLLOVER_FILES = 10
    ,RESERVE_DISK_SPACE = OFF
    )
WHERE
    [statement] > 'TRUNCATE TABLE'
AND [statement] < 'TRUNCATE TABLF'

However, there will be occasions where you need to use a LIKE operator. For example;

WHERE [database_name] LIKE '%Audit'