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?