SQL Server - How to audit permission errors on objects using SQL Profiler (Trace)

Views: 641
Reading Time: 6 minutes

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.

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

Start SQL Server Management Studio and open SQL Server Profiler:
sql-server-sql-server-profile-trace

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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions

Now is the time to select the event we are going to monitor, which is the “Audit Schema Object Access Event” and select the columns we want to be recorded in the Trace table:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-2

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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-3

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…”

Now click on the “Run” button and the trace will start on the instance:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-5

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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-4

If we go back to the SQL Server Profiler window, we will see that this access attempt has been logged:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-6

Or we can query the table where we set where Trace should save the results:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-7

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:

sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-8

sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-9

And this will be the result of exporting Trace as a SQL script:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-10

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 Trace file name “InsertFileNameHere” to a path accessible by your server, as I did in the example below:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-11

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:

sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-12

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.

sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-13

Once we have retrieved our trace ID, let's now stop it and make sure it is no longer active:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-14

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.