August 9, 2017 at 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'.
August 9, 2017 at 4:20 pm
sqlGDBA - Wednesday, August 9, 2017 3:52 PMAs 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
August 10, 2017 at 11:42 am
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