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?

Solution 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

Solution 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'

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

Comments

[KM.]

@gbn, yea, it is "junk" query I use from time to time, see latest edit, I reformatted and reorganized the joins

[jp2code]

Using this SELECT command, I was able to find that the stored procedure that I wrote has NO Permissions. How do I now set one?

[Adriaan Davel]

@jp2code just to clarify, your stored procedure had no EXPLICIT permissions set on it, but it would have the inferred / inherited permissions, e.g. sysadmin can still execute it