TSQL query to verify permissions and object exists

Question

I am writing a query that will return

1. if a table or stored proc exists within a database
2. if a particular role has been assignned execute permissions to execute that particular stored proc.

I guess I need to query the master database and wiggle thru that to get to the database and the table or stored proc that I am looking for. How can I get a 'true / false' if execute permissions have been assigned to a particular role on that object?

Answer 1

USE YourDatabase

/*To find out if it exists*/

SELECT OBJECT_ID('dbo.spFoo') /*Will be NULL if it doesn't exist or you don't have 
                                permission to see the object*/

/*To find out the permissions on it take a look at the following system views*/

select * from sys.database_permissions p
inner   JOIN sys.database_principals dp
   on     p.grantee_principal_id = dp.principal_id
   where major_id=object_id('dbo.spFoo')