2019-09-17

How to Audit Login and Logout for specific logins

Starting from SQL Server 2008, we can use SQL Server Audit feature to record numerous actions occurred on our SQL Server. SQL Server Audit uses Extended Events under the hood, which impose lesser loading than the plain old SQL Trace.
In this blog post, I would like to show you how to audit login and logout events for specific logins, which can only be done by setting a filter on the Server Audit.

USE [master]
GO

CREATE SERVER AUDIT [Audit_sysadm_login]
TO FILE
(    FILEPATH = N'D:\SqlAudit\'
    ,MAXSIZE = 10 MB
    ,MAX_FILES = 100
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 5000
    ,ON_FAILURE = CONTINUE
)
WHERE ([server_principal_name]='sa' OR [server_principal_name]='peter.lee')
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec_sysadm_login]
FOR SERVER AUDIT [Audit_sysadm_login]
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO

ALTER SERVER AUDIT [Audit_sysadm_login] WITH (STATE = ON)
GO


* Please be reminded that ON_FAILURE is a critical argument on the CREATE SERVER AUDIT statement. If your SQL Server service online is more important than the auditing needs, set this argument to CONTINUE.

The simplest way to check the audit log is using the Log File Viewer, which can be opened in Object Explorer > Security > Audits > right-click the audit > View Audit Logs.