How to collect the users Enabled/Disabled SQL logs?
The Enable/Disable logs will be recorded in the Event Viewer in the following scenarios:
In SQL Server Management Studio, Security ⇾ Logins ⇾ Right-click on any user ⇾ Properties ⇾ Status ⇾ Login section ⇾ select Disabled/Enabled.
By executing the query ALTER LOGIN <user_name> <ENABLE/DISABLE> (i.e., the event should be populated in the event viewer with ACTION_ID = LGEA for enabled user action and ACTION_ID = LGDA for disabled user action).
If the logs are not available in the Event Viewer, then the EventLog Analyzer will not be able to produce SQL Reports for Enable/Disable users.
To fix this, check if "SERVER_PRINCIPAL_CHANGE_GROUP" is present in the "Audit Action Type" of the enabled Server Audit specifications. If this is true, then check if the event logs are getting overwritten due to the generation of a lot of events.
New to ADSelfService Plus?
Related Articles
Why are SQL Server audit logs not collected?
Case 1: Is Advanced Auditing enabled? Open EventLog Analyzer and go to Settings > Database Audit > SQL Servers. The DDL/DML Monitoring column should show Manage for the required instance. If it says Not configured, then edit the required instance, ...How to add an SQL server in EventLog Analyzer
Objective This article focuses on how to add SQL servers in EventLog Analyzer and receive the reports of SQL instances. Prerequisites Have access to the EventLog Analyzer console as an administrator Have access to the SQL server for manual ...Why are some SQL Server reports showing no data?
Case 1: Are the required audit policies configured? Open SQL Server Management Studio application in the Windows machine in which SQL Server is installed, and connect to the required instance. Click the Security option. The Server Audit ...What are the audit policies required to generate events for an SQL Server report?
Existing Reports vs SQL Server Policies S. no. Report Group Total Reports Report Name Criteria Required Server-level Audit Action Types 1 SQL Server Events 2 All Events - - Important Events - - 2 SQLServer Trend Report 2 Read Event Trend ...How to collect historic logs from Windows devices in EventLog Analyzer
Objective When a Windows device is onboarded in EventLog Analyzer, log collection starts from the moment of onboarding. To retrieve Windows event logs generated before the onboarding, you can use the following methods: Historic log collection: Can be ...