Unable to create Server Audit with where clause on SQL 2008 R2

  • As per the documentation here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql we should be able to create a server audit with a where clause(IT says versions starting with 2008). However, I cannot get this simple audit to work:


       CREATE SERVER AUDIT [Poilcy_ServerAudit]
        TO FILE
        (    FILEPATH = N'C:\Temp\'
            ,MAXSIZE = 100 MB
            ,MAX_ROLLOVER_FILES = 10
            ,RESERVE_DISK_SPACE = OFF
        )
        WITH
        (    QUEUE_DELAY = 30000
            ,ON_FAILURE = CONTINUE
        )
        WHERE (server_principal_name <> 'abc')

    I get the below error message. Any version greater than that is successful(SQL 2012/2014/2016). Any idea, anyone ?

    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'WHERE'.

  • sqlGDBA - Wednesday, August 9, 2017 3:52 PM

    As per the documentation here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql we should be able to create a server audit with a where clause(IT says versions starting with 2008). However, I cannot get this simple audit to work:


       CREATE SERVER AUDIT [Poilcy_ServerAudit]
        TO FILE
        (    FILEPATH = N'C:\Temp\'
            ,MAXSIZE = 100 MB
            ,MAX_ROLLOVER_FILES = 10
            ,RESERVE_DISK_SPACE = OFF
        )
        WITH
        (    QUEUE_DELAY = 30000
            ,ON_FAILURE = CONTINUE
        )
        WHERE (server_principal_name <> 'abc')

    I get the below error message. Any version greater than that is successful(SQL 2012/2014/2016). Any idea, anyone ?

    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'WHERE'.

    That documentation also has this:
    predicate_expression
    Applies to: SQL Server 2012 through SQL Server 2017.

    And then in the where clause example it has this:
    WHERE <predicate_expression>

    I know there is the other predicate_expression later but I believe the availability applies to both.

    The whole combining the docs like this for different versions has a lot of similar problems. Sometimes you just need to dig until/if you can find the documentation for the version you are on. For SQL Server 2008R2, this is the buried documentation - where clause not included:
    CREATE SERVER AUDIT (Transact-SQL)

    Sue

  • You are correct. Not sure how I missed that ! Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply