Hello people,
Good Morning!
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
Start SQL Server Management Studio and open SQL Server Profiler:
How to configure Trace events and filters in SQL Server Profiler
Once opened, configure the description of your trace, where the results will be saved (I always prefer in table), and other settings you find pertinent:
Now is the time to select the event that we are going to monitor, which is the “Audit Schema Object Access Event” and select the columns that we want to be recorded in the Trace table:
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
Now click on the “Run” button and the trace will start on the instance:
Viewing Trace Results in SQL Server Profiler
I will now use a user without permission and try to query a table that will generate a missing permission error:
If we go back to the SQL Server Profiler window, we will see that this access attempt has been logged:
Or we can query the table where we set where Trace should save the results:
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:
And this will be the result of exporting Trace as a SQL script:
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).
First, we open the SQL file generated in SQL Server Management Studio and change the name of the Trace file “InsertFileNameHere” to a path accessible by your server, as I did in the example below:
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.
|
1 |
select * from sys.traces |
Once we have retrieved our trace ID, let's now stop it and make sure it is no longer active:
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.
