On Sunday I was responding to a question in one of the forums where the jobs were failing with permission denied error. During the troubleshooting I wanted to check if the permission for those stored procedures were explicitly denied for the SQL Server Agent/Job owner account.
On SQL Server 2005 and above, I usually use the below script to check the permissions granted/denied for database users.
SELECT USER_NAME(dppriper.grantee_principal_id) AS [UserName], dppri.type_desc AS principal_type_desc, dppriper.class_desc, OBJECT_NAME(dppriper.major_id) AS object_name, dppriper.permission_name, dppriper.state_desc AS permission_state_desc FROM sys.database_permissions dppriper INNER JOIN sys.database_principals dppri ON dppriper.grantee_principal_id = dppri.principal_id
The output of this script would be like the one in this example.
But on that instance, the permission was not denied for any of the stored procedures. Still trying to figure out the possibilities.