SQL Server
SQL Server auditing in EventLog Analyzer
In this page
- Implementation of SQL Server logging in EventLog Analyzer
- Why is SQL Server logging important and how is it enabled by EventLog Analyzer?
Implementation of SQL Server logging in EventLog Analyzer
To be able to collect logs from the SQL Server, it is required to create an SQL Server Audit object. This can be done using the SQL Server Management Studio.
- In MS SQL Server Management Studio, navigate to Object Explorer.
- In the Object Explorer, expand the Security node.
- Right-click the Audits folder and select the New Audit option. It will open a Create New Audit page.
- Define the Audit Name field with a suitable name for the Audit Object.
- Choose the Application log type in the Audit Destination field.
- Accept the other default settings and click Save below the new audit specifications.
Once the Audit Object is created, the SQL Server instance can be added to the database management page in EventLog Analyzer.


You can also choose credential validation for each of the SQL instances by selecting the instance name and clicking Next. This will take you to the Credential Configuration page where you can enter the Username and Password.

After you've added the SQL instance in EventLog Analyzer, SQL Server auditing can be done in different ways.
1. DDL/DML monitoring
In EventLog Analyzer, a server-level audit specification is created in the SQL Server instance for the following audit policies. This is done only when the advanced auditing option is enabled for the server instance.
- SCHEMA_OBJECT_ACCESS_GROUP: Tracks access to schema objects within the SQL Server database. It records events whenever there is an attempt to access or modify schema objects such as tables, views, stored procedures, functions, etc.
- DATABASE_ROLE_MEMBER_CHANGE_GROUP: Monitors changes to database roles and their members. It captures events when users are added to or removed from database roles, providing insights into changes in database access permissions.
- SERVER_ROLE_MEMBER_CHANGE_GROUP: Similar to the database role member change policy, this audit policy tracks changes to server roles and their members. It records events related to the addition or removal of users from server roles, ensuring visibility into server-level access control changes.
- FAILED_LOGIN_GROUP: Logs failed login attempts to the SQL Server instance. It captures events when authentication attempts fail due to incorrect credentials or other authentication issues, aiding in the detection of potential security threats or misconfigurations.
- SUCCESSFUL_LOGIN_GROUP: Records successful login attempts into the SQL Server instance. It provides an audit trail of successful authentications, helping administrators monitor user activity and ensure compliance with security policies.
- DATABASE_CHANGE_GROUP: Tracks changes made to the database configuration settings. It captures events related to alterations in database settings, such as collation changes, database options modifications, or other configuration adjustments.
- DATABASE_OBJECT_CHANGE_GROUP: Monitors modifications to database objects, such as tables, views, procedures, and functions. It records events whenever there are changes made to the structure or properties of these database objects.
- DATABASE_PRINCIPAL_CHANGE_GROUP: Audits changes to database principals, including users and roles. It captures events when the properties of database principals are modified, providing visibility into changes in database access permissions.
- SCHEMA_OBJECT_CHANGE_GROUP: Focuses specifically on changes to schema objects within the database. It records events when alterations are made to schema objects like tables, views, stored procedures, etc.
- SERVER_PRINCIPAL_CHANGE_GROUP: Monitors changes to server-level principals, including logins and server roles. It captures events related to modifications in properties or permissions of server-level principals, helping administrators track changes in server access control.
- LOGIN_CHANGE_PASSWORD_GROUP: Tracks changes to login passwords within the SQL Server instance. It records events whenever passwords for SQL logins are modified, ensuring administrators are aware of any changes to login credentials.
- SERVER_STATE_CHANGE_GROUP: Monitors changes to the state of the SQL Server instance. It captures events when the server is started, stopped, paused, or resumed, providing visibility into the operational status of the server.

The audit policies act as the intermediary between the SQL Server instance and EventLog Analyzer and facilitate the auditing. After the audit, the results can be seen in the following report groups:
- SQL Server Events
- SQL Server Trend Report
- DDL Auditing Report
- DML Auditing Report
- Auditing Account Management
- Auditing Server Report
- Attack Reports
- Additional Security Reports
2. Database auditing
EventLog Analyzer queries the SQL Server instance on an everyday basis to fetch logs from it.
Upon auditing them, the following report groups are populated with the result:
- Last Login Time Report
- Delete Operations Report
- Logins Information Report
- Most Used Tables
- Table Update Report
- Index Information Report
- Server Information Report
- Waits Information Report
- Blocked Processes Report
- Schema Change History
- Object Change History
- Connected Applications Report
- Security Changes Report
- Permissions Information Report
- Last Backup of Database
- Last DBCC Activity
3. Column Integrity Monitoring
Column Integrity Monitoring can be configured by creating a trigger within the SQL Server instance, which autonomously generates an event in the Event Viewer whenever a modification is detected in the monitored column of a specified table. The resulting Column Integrity Monitoring report furnishes comprehensive details regarding the alterations observed within the monitored column. It's important to note that certain data types such as text, ntext, and images are exempt from monitoring.
The audit findings are conveniently presented within the Column Modified Report, offering users a clear insight into the integrity of their data.
Why is SQL Server logging important and how is it enabled by EventLog Analyzer?
SQL Server logging is a critical aspect of database management, involving the recording of various events, activities, and transactions within the SQL Server environment. Logging serves multiple purposes, including troubleshooting, performance analysis, security auditing, compliance management, and disaster recovery.
Troubleshooting: SQL audit logs capture an array of critical events such as DDL and DML actions, password modifications, login adjustments, server initiation or failure, and numerous other occurrences. These recorded events play a pivotal role in identifying and resolving any anomalies or unexpected behaviors within the SQL Server instance. Transaction logs enable point-in-time recovery and database restoration to restore data consistency and recover from system failures or data corruption. EventLog Analyzer offers various reports to achieve these functionalities. A few examples include:
-
DML Auditing Report
-
DDL Auditing Report
-
Auditing Account Management
-
Column Modified Reports and Column Integrity Monitoring
Performance analysis: SQL Server auditing logs track database modifications and provide insights into query execution times, frequency of used tables, connected applications, and blocked processes in the SQL Server instance, which can help in performance tuning. Reports in EventLog Analyzer that facilitate this process are:
-
Connected Applications
-
Most Used Tables
-
Blocked Processes
Security auditing: SQL Server audit logs record user activities, login attempts, and database changes to monitor access patterns and detect unauthorized actions. The following reports in EventLog Analyzer help with security auditing:
- Server Principal Changes
- Authority Changes
-
Failed Logon Events
-
Logon and Logout Events
Compliance management: SQL Server logs facilitate compliance with regulatory requirements by documenting data access, modifications, and security-related events for auditing and reporting purposes.