As technology continues to advance, many businesses are looking to upgrade their infrastructure and take advantage of the new capabilities offered by SQL Server. One such capability that has gained attention is SQL Auditing. In this article, we will explore the concept of SQL Auditing and its importance in maintaining compliance.

During a recent engagement with a bank, they expressed their interest in implementing SQL Auditing as part of their compliance requirements. As a consultant specializing in performance tuning, I was intrigued by their request and decided to assist them in understanding and implementing this feature.

One of the first issues that came up during the implementation was the incorrect time displayed in the audit logs. Upon investigation, it was discovered that the SQL audit records the time in UTC rather than the local time. This meant that there was a significant time difference between the actual event and the recorded time in the logs.

To address this issue, we utilized a simple workaround by converting the UTC time to the local time using the following query:

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time_afterconvert
    ,GETDATE() AS 'Current_system_time'
    ,*
FROM fn_get_audit_file('G:\DATA\*', DEFAULT, DEFAULT)

By implementing this query, we were able to accurately display the event time in the local time zone, ensuring that the compliance reports reflected the correct timestamps.

SQL Auditing is a powerful tool that allows businesses to track and monitor activities within their SQL Server environment. It provides valuable insights into user actions, changes to roles and logins, and other critical events. By leveraging SQL Auditing, businesses can ensure compliance with industry regulations and maintain a secure and accountable database environment.

When it comes to auditing SQL Server activities, there are various options available. Some businesses prefer to use third-party solutions that offer additional features and customization options. However, SQL Server’s built-in auditing capabilities are often sufficient for most organizations.

What tool do you use to audit SQL Server activities? Are you utilizing the built-in auditing features or do you prefer third-party solutions? Share your thoughts and experiences in the comments below.