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.
A well-known utility for most SQL Server DBA's, but not so much for developers, SQL Server Profiler has many other uses besides auditing, especially with the growth of systems that use 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 concerned only with coding in his programming language (Java, C #, etc.) and does not have 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 you can perform the most diverse types of conditions for this session to be audited. I will list the most common:
- ApplicationName: Filter that is already enabled by default and with the NOT LIKE clause "SQL Server Profiler - <hash>" so that the Trace session itself is not audited, 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.
Remember that when editing filters, if you want to add more than one value to the filter clauses, just hit the “Enter” key when the cursor is positioned in the text box, so that you see more text boxes below:
In this example, I applied the “Exclude rows that do not contain values” filter to the DatabaseName and DBUsername columns so that indirect access attempts such as SQL Prompt and Intellisense perform, preventing not-so-interesting information from being written to the log and leaving it too much. "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 menu option “File”> “Properties…”
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.