Determining Object Access Using SQL Server Profiler

June 18, 2010

Deanna Dicken examines a SQL Server Profiler event to determine object access...who is using
the object, when, and how. This gives the SQL developer or database administrator much needed
information for impact analysis prior to a change or the decommissioning of a SQL Server object.

Overview

I had a dilemma the other day. I have
a feeling there are quite a few folks out there in a similar predicament, so I
thought I'd share. We have some legacy tables that were created in one of our
databases. The problem is they no longer serve the purpose they were originally
intended for 10 years ago. We'd like to get rid of them, but how can we be sure
no one is coming after that data anymore (note that there is an ETL-type
process still loading these tables regularly)?

Of course, SQL Server Profiler comes
to mind right away, but how do you keep from tracing everything and adding a
bunch of additional overhead on your production database? Then, how do
efficiently dig through all of that trace data? There has to be a way to narrow
it down.

Audit Schema Object Access...I
found this little gem the other day while I was looking for that better way.
This is an event class in SQL Server Profiler that monitors for usage of permissions
assigned to an object (e.g. SELECT, INSERT, EXECUTE). Here I'll show you how to
set it up and what kind of data you can get from using it.

Setting up Profiler

When you open up SQL Server Profiler
and create a new trace, the Audit Schema Object Access event is not immediately
available. To get to it, go to the event selection tab on the Trace Properties
window. Check the "Show all events" and "Show all columns"
options.

Now once you have this setup, you
could let the trace start, but this would give you object access data on every
object in your database. To narrow the results down to a subset of the objects
in the database, click the "Column Filters..." button. The following
screen is opened and you can add your object filter on ObjectName. Note that if
you forget to check "Show all columns" on the previous screen, the
ObjectName column will not be available on the Edit Filter screen.

I've placed my filter on the
Person.Contact table in the AdventureWorks sample database. If you want to
trace on more than one object you can just hit enter after the first object
name and another textbox will appear to allow you to enter the next one. If you
need to trace the whole database, except for a table or two, just place your
filter in the Not Like section instead.

Now hit OK to save the filter and Run
to start the trace. In SQL Server Management Studio, I'll run a couple of commands
against the Person.Contact table and we'll take a look at the results of the
trace. Here is the script I will run:

SQL Server Profiler shows us three
rows of object access events. From the values in the TextData column, you can
see the SQL statement that triggered the audit event. The first one is the
SELECT statement, the second is the UPDATE statement, and then we see the
UPDATE statement again. This is due to the ROLLBACK issued on the transaction
containing the UPDATE. Scroll to the right to see additional data collected
during the event such as the user that executed the statement, database name,
SPID, start time, host name, and even a transaction number so you can correlate
events in the trace.

You can also see access events that
failed using this same setup. I have another user, kschmoe, who doesn't have
access to the Contact table. If we run the same query as above under his
credentials, here's the trace information we get for that event. You see the
same three object access event rows.

However, the user received these
errors from SQL Server for attempting to run that batch.

So if the trace looks the same for
both users where one has sufficient rights and the other does not, how do you
know if the user was successful in accessing the object? Well, scroll out...way
out...to the right in your trace results and you'll see a column called success.
Success is set to 0 for the user that didn't have sufficient privileges to
carry out their object access. Carrying this thought out a little further, you
could add a filter to your trace on Success = 0 to monitor your objects for
insufficient privilege access.

There's one more little twist I want
to share with you before closing. If I were to wrap the select statement from
the batch above into a stored procedure, what do you think the trace would show
then? Let's find out. Here's the stored procedure for those of you playing the
home game.

The screen below shows the results of
the trace on the stored procedure call. However, there's no stored procedure
listed in TextData, you say. Correct, the audit event for the schema object
access only shows the command from the stored procedure that was responsible
for triggering the audit event. If you want to know what stored procedure
contained that call, you'll have to look it up.

Conclusion

The Audit Schema Object Access event
class in SQL Profiler provides a means to see exactly who is coming after your
data, when, and in what way. As a data owner, this gives you the information
you need to make sure the data is being used for the right reasons and in the
right way. Or, to make sure it isn't being used before you blow it away.