SSDT Server Audit object does not recognise LIKE operator in the WHERE predicate

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

Resolved

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

Type:	Bug
ID:	780003
Opened:	2/25/2013 8:32:43 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

SQL Server 2012 - Enterprise Edition

Developer Tools (SSDT, BIDS, etc.)

Windows 7 Professional

English

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%'

Building the project results in a failure with the LIKE operator reported as a syntax error

The project should build. The project should be able to be deployed and the server audit created on the server.

X64

0 attachments

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'