Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there any easy way to see those events in SQL Server 2008 for which permission was denied on a query (along with the text of the query and the specific object that permission was denied to)?

I have an application which is performing a lot of operations on the database, one of which is having permission denied. I need to find out exactly what query is being denied, so that I can work out exactly the minimum set of additional permissions I need to give to the database role in question in order to have all operations success.

I've tried using SQL Profiler, and selecting to record only the events Security Audit/Audit Schema Object Access Event, which looks like it'd almost do the job. However that logs successful as well as failed events, and so far as I can see doesn't show anything in the UI to distinguish them - so I can't tell which of the hundreds of events it throws up is the one I'm interested in. Is there any other way to capture just the failed SQL statements?

Thanks

share|improve this question
up vote 0 down vote accepted

To answer my own question :-) After some more experimenting, it turned out that in SQL Profiler, when you set up the events to profile, there's an option to choose columns, and one of the columns you can add is a 'Success' column which - in the case of the Audit Schema Object Access event - tells you whether permission was granted or not. So adding that column and then looking for the '0' in that column in the events logged solved the problem for me.

I'm not sure whether there is a better solution, but I'm fixed now.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .