SQL Server Profiler - watch for permission denied on objects?

sql-server permissions sql-server-profiler
SqlRyan picture SqlRyan · Jun 26, 2009 · Viewed 8.8k times · Source

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

Mike Forman picture Mike Forman · Jun 26, 2009

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

Related questions

GRANT EXECUTE to all stored procedures

Does the following command effectively give the user, "MyUser," permission to execute ALL stored procedures in the database? GRANT EXECUTE TO [MyDomain\MyUser]

Read More
How do I change the owner of a SQL Server database?

When I accidentally click on the Database Diagrams tab, I get one of the following errors: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of …

Read More
The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'

SETUP: SQL Server 2005 & DotNetNuke 05.01.02. This started with me trying to install a DNN Module that had "select * from dbo.sysobjects" in it's SQL scripts. That failed with the following error: The SELECT permission was denied on the object 'sysobjects', …

Read More