Auditing SQLServer logins

by

How to audit SQLServer logins?

We can audit SQL Server logins using multiple ways like running a profiler, Logon triggers and creating a audit.

 

Below script will create a audit to capture the login events. We can capture the login in event logs or file.

Below is example to capture login events in file.  Replace D:\Audit\ in below script to a valid folder

 

USE [master]
GO

CREATE SERVER AUDIT [TestAudit]
TO FILE 
(	FILEPATH = N'D:\CASEDATA\'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = SHUTDOWN
	,AUDIT_GUID = 'b305ddf6-3487-4c77-afc2-fc8cfc357abc'
)
GO

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20130205-205300]
FOR SERVER AUDIT [TestAudit]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = OFF)
GO

Advertisement