Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get permissions for stored procedure in sql server 2005

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

like image 259
Tim Abell Avatar asked Sep 15 '09 15:09

Tim Abell


People also ask

How do I view stored procedure permissions in SQL Server?

Right click on your procedure and select Properties. You'll get the following window. As shown inthe preceding image, go to Permissions tab and click on Search button. On click you'll get a window to select user and roles, click on Browse to select users that require permission and click OK.

How do I grant permission for a stored procedure in SQL Server?

Use SQL Server Management StudioExpand Databases, expand the database in which the procedure belongs, and then expand Programmability. Expand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page.


3 Answers

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

like image 69
gbn Avatar answered Sep 20 '22 02:09

gbn


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'
like image 30
KM. Avatar answered Sep 19 '22 02:09

KM.


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

like image 43
Yordan Georgiev Avatar answered Sep 19 '22 02:09

Yordan Georgiev