Determining Object Access Using SQL Server Profiler

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:

SELECT TOP 10 [ContactID]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailAddress]
      ,[EmailPromotion]
      ,[Phone]
      ,[PasswordHash]
      ,[PasswordSalt]
      ,[AdditionalContactInfo]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Person].[Contact]
  
  BEGIN TRAN
  UPDATE Person.Contact
  SET EmailPromotion = 0
  WHERE EmailPromotion = 2
  
  ROLLBACK

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.

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Contact', database 'AdventureWorks', schema 'Person'.
Msg 229, Level 14, State 5, Line 19
The SELECT permission was denied on the object 'Contact', database 'AdventureWorks', schema 'Person'.
Msg 229, Level 14, State 5, Line 19
The UPDATE permission was denied on the object 'Contact', database 'AdventureWorks', schema 'Person'.

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.

CREATE PROCEDURE trace_test AS
BEGIN
SELECT TOP 10 [ContactID]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailAddress]
      ,[EmailPromotion]
      ,[Phone]
      ,[PasswordHash]
      ,[PasswordSalt]
      ,[AdditionalContactInfo]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Person].[Contact]
 END
 GRANT EXECUTE ON trace_test TO jschmoe 

Now, jschmoe runs:

EXEC trace_test 

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.

Additional Resources

Introduction to SQL Profiler
MSDN Audit Schema Object Access Event Class
MSDN Using SQL Server Profiler

» See All Articles by Columnist Deanna Dicken