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.
- Open Netwrix Auditor → Navigate to Reports | Predefined | SQL Server | All SQL Server Logons → Click “View”.
Now you can view all users that access your SQL database.