Get permissions for stored procedure in sql server 2005
SELECT OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_nameFROM sys.database_permissions pWHERE OBJECT_NAME(major_id) = 'MyProc'
You can tweak this to join to sys.database_principals
, or sys.objects
if you want too
try (NOTE: works for more than stored procedures):
SELECT dp.NAME AS principal_name ,dp.type_desc AS principal_type_desc ,o.NAME AS object_name ,o.type_desc ,p.permission_name ,p.state_desc AS permission_state_desc FROM sys.all_objects o INNER JOIN sys.database_permissions p ON o.OBJECT_ID=p.major_id LEFT OUTER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id WHERE o.NAME = 'YourProcedureName'
Kind of off topic, but ... you could enable you development db to "remember" the permissions it has had on different objects and keep them during development time regardless of how-many times you drop and create an object ...