Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.