Going Beyond SQL Server Default Trace to Stay Abreast of Activity

Database administrators and operators need to keep an eye on activity across database instances. Creating traces in Transact-SQL such as SQL Server default trace can help you stay on top of events that happen across your SQL servers and ensure seamless performance and ongoing access to applications and data users might need. By enabling this kind of security audit, you can regularly review the information from the trace on Microsoft SQL Server Profiler, identify the root cause of the problem and spot aberrant activity. However, be ready to face the limitations of native tools.

Facing Pitfalls When Relying Only on Trace in SQL Server Profiler

Although SQL Server offers a variety of tools for auditing, SQL Server tracing with the minimum-weight default trace stands out the most; it is switched on by default and can be used immediately. The information from the trace on the SQL Server Profiler provides insights into what happened within just one server.

However, because the default trace contains all of the occurred tasks, be ready to put in labor into manual browsing through SQL Server trace events on each server that you have and write queries to filter out SQL Server trace file by columns to put the pieces of the puzzle together. Plus, using SQL Server audit trace files requires you to be fluent in Transact-SQL and spend extra time and effort to analyze SQL Server default trace (for instance, to drill down to the root cause of application error warnings caused by incorrect queries).

Complementing Audit Trace from SQL Server with Netwrix Auditor

Netwrix Auditor for SQL Server complements the capabilities of the default trace by delivering actionable information that correlates raw SQL Server trace events into ready-to-use and easy-to-read reports showing who-what-when-where details for every change and access event that occurs on your servers. You can view the complete trail with the activity from all of your SQL servers within a single pane of glass. Additionally, the Interactive Search feature enables you to further chase down illicit activity and get to the root cause of a problem so that you can prevent database unavailability and corruption. Plus, alerts on threat patterns will help you defend your critical assets against inside misuse and external attacks.

Interactive Search from Netwrix Auditor, report on database login events: Object type, Action, Who, What, When, Where