Get permissions for stored procedure in sql server 2005

Question

How do I get the granted permissions for a stored procedure in sql server 2005?

Answer 1

SELECT
    OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
FROM
    sys.database_permissions p
WHERE
    OBJECT_NAME(major_id) = 'MyProc'

You can tweak this to join to sys.database_principals, or sys.objects if you want too

Answer 2

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'

Answer 3

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 ...