In today's post I will demonstrate how to create a trace in SQL Server using SQL Profiler to audit object accesses, whether by a specific user, object or specific database. This is especially useful for identifying which users have access to a particular object or also finding out which users do not have access to an object and yet trying to access it, be it a table, view, stored procedure, etc. In many companies, this is used to audit access to critical tables with sensitive information such as employee data and salaries, etc.
Well-known utility of most SQL Server DBA's, but not so much on the part of the developers, SQL Server Profiler has many other uses besides performing audits, mainly with the growth of systems using ORM's, such as Hibernate and Entity Framework, for encapsulation database queries. These tools are responsible for generating SQL queries and queries for the developer, who is only concerned with coding in his programming language (Java, C #, etc.) and does not need to write a line of SQL code.
Usually these queries are not very performative and the developer often doesn't know how the query was assembled by ORM. When system performance issues start to occur, SQL Server Profiler is a powerful tool, enabling you to identify which exact command the system is running and allowing DBA to perform proper performance analysis, index analysis, and so on.
How to open SQL Server Profiler through Management Studio
How to configure Trace events and filters in SQL Server Profiler
To adjust the trace capture filters, click on the “Column Filters” button and a window will open where it will be possible to perform the most diverse types of conditions for this session to be audited. I'll list the most common ones:
- ApplicationName: Filter that is already activated by default and with the NOT LIKE clause “SQL Server Profiler - "So that the Trace session itself is not audited, it allows filtering by the software used to connect to the database, such as" Microsoft SQL Server Management Studio - Query "or" .Net SqlClient Data Provider ".
- Databasename: Allows you to filter databases that may or may not be part of the trace.
- DBUsername: Allows you to filter which SQL Server users may or may not be part of the trace.
- HostName: Filter that lets you select which hostnames (hostnames) to audit or not
- LoginName: Defines whether you want to apply a filter to session login (can be AD or SQL Server login) so that it is part of Trace or not
- ObjectName: Allows filtering objects (tables, views, stored procedures, etc.) that may or may not be part of the trace.
- SPID: Filter that allows you to select which sessions will be audited or not by Trace
- Success: Essential parameter for this post, which defines whether the session was able to access the object (1) or failed due to permission error (0). To achieve the purpose of this post, you must use the Equals to 0 condition.
It is worth remembering that when editing the filters, if you want to add more than one value in the filter clauses, just press the “Enter” key when the cursor is positioned in the text box, so that more text boxes appear below:
In this example, I applied the filter “Exclude rows that do not contain values” in the DatabaseName and DBUsername columns so that indirect access attempts, as SQL Prompt and Intellisense perform, preventing less interesting information from being written to the log and leaving it too "polluted".
If you have started Trace and let it edit, just stop Trace using the Stop button (or in the File> Stop Trace menu) and select the “File”> “Properties…” menu option
Viewing Trace Results in SQL Server Profiler
Exporting Trace Definitions to SQL File
SQL Server Profiler also gives us the option to export the settings and filters performed as a .sql file, so you can turn this trace back on more quickly and conveniently, and you can even automate this in a Job, for example:
Note that when you export trace to .sql, data export to database table is NOT supported.
Querying SQL Script-enabled Trace Result
As explained above, using script we can easily enable trace, but it is not possible to export data to a table, only to Trace (.trc) files. Fortunately, this is not a problem as we can easily perform queries on the trace file using the fn_trace_gettable function. Using SQL Server Standard Trace to Audit Events (fn_trace_gettable) e Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable).
Then we will try to look up the table and redisplay the access denied message. Now that the message has been logged in our trace file, we query:
Identifying and stopping a trace event
If you choose to start trace events using the SQL script, it is very important to be able to identify if the trace is active and to know how to stop it. To do this, simply execute the query below.
select * from sys.traces
And that's it folks!
The idea was to create a specific object access audit post and it turned out to be a SQL Server Profiler mini tutorial.
I hope you enjoyed the post and see you next time.