SQL Server Profiler - watch for permission denied on objects?

Question

I'm using SQL Profiler to watch some database activity, and I'm interested in watching "Permission Denied" for various objects in the database. There are about 50 different items in the "Security Audit" category, but none that seem to display "Permission denied" for an object. I can see Login Failed, and a few other failures, but nothing on the object level.

Example: A user has permission to a database, but not on a particular table. When they attempt to select from that table, they'll receive an error - can I have profiler watch for these errors?

Answer 1

You want to capture the "User Error Message" event in the "Errors and Warnings" section. You can filter the Error column as you see fit. Error 229 is permission denied

Answer 2

This might achieve the same:

Back in SQL 2000, you could modify error message 229 so "permission denied" is written to the SQL error log.

EXEC dbo.sp_altermessage 229, 'WITH_LOG', 'true'

I don't know if/how this works for SQL 2005+ probably not, says Connect but maybe we can now says Tibor Karaszi. I can't test myself, sorry.

Edit: This has the advantage that you don't need profiler or a tracer running...