How to Audit SQL Server Logins

Native Auditing

To audit SQL Server logins, we need to configure SQL Server Auditing settings, which were introduced in SQL Server Database 2008.

Connect the SQL server instance via SQL Server Management Studio.
Navigate to Security → Right-click “Audits” and select “New audit” → Type in an name for the audit and select the location where the SQL Server audit logs will be stored → Click “OK” → Right-click the newly created audit and select “Enable audit”.
Right-click “Server Audit Specification” and select “New Server Audit Specification” → Type in an appropriate name → Select the new audit from the audit drop-down menu → In the “Audit action type” column, select “Failed Login Group” and “Successful Login Group” → Click “OK” → Right-click on the newly created server audit specification and select “Enable server audit specification”.
To view the SQL Server audit login trail, navigate to Security | Audits → Right-click the newly created audit and select “View Audit Logs”

Now you can read all login audit events in your SQL database.

Lokasi:

Voyage