CODEWITHSUNDEEP
sqlsql-serversql-server-2005securitypermissions
Tim Abell
Tim Abell

Reputation: 11870

Get permissions for stored procedure in sql server 2005

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

Upvotes: 16

Views: 30165

Answers (3)

Yordan Georgiev
Yordan Georgiev

Reputation: 5420

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

Upvotes: 1

KM.
KM.

Reputation: 103579

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'

Upvotes: 12

gbn
gbn

Reputation: 432180

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

Upvotes: 29

Related Questions