Error when creating Audit Specification in SQL Server database

Question

Hi, I'm trying to create an Audit specification called SuccessfulLoginAttempt to track all successful connections to our SQLServer 2019 standard edition. I have already created the Server Audit but whenever I run the create script for audit specification, I get the error 'Incorrect Syntax near 'SUCCESSFUL_LOGIN_GROUP'

Here is my script;

CREATE DATABASE AUDIT SPECIFICATION SuccessfulLoginAttempt

FOR SERVER AUDIT LoginAttempt

ADD (SUCCESSFUL_LOGIN_GROUP)

WITH (STATE=ON);

GO

What's wrong with the syntax? hopefully, Someone can help. Thanks in advance

Answer 1

Hi @Babawale Dawodu

Do you want to create a database audit specification or a server audit specification?

Example of creating a server audit specification:

CREATE SERVER AUDIT SPECIFICATION HIPAA_Audit_Specification  
FOR SERVER AUDIT HIPAA_Audit  
    ADD (FAILED_LOGIN_GROUP)  
    WITH (STATE=ON);  
GO  

If you change your syntax to create a server audit specification, the error seems to disappear.

Best regards,

Percy Tang

Answer 2

I think it is because SUCCESSFUL_LOGIN_GROUP is a server event, so it cannot appear in a database audit.