获取sql server 2005中存储过程的权限

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

如何在sql server 2005中获取存储过程的授予权限？

3 个解决方案

#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

您可以调整它以加入sys.database_principals或sys.objects（如果您也需要）

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

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

有点偏离主题，但是......你可以让你的开发数据库“记住”它对不同对象的权限，并在开发时保留它们，无论你放弃多少次并创建一个对象......

#1

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