开发者

SQL Server 2000 - View list of sprocs with GRANT EXECUTE for a particular role exclusively?

There are approx 500 sprocs in my SQLSERVER 2000 database; each sproc has a typical Grant Execute statement similar to the following.

GRANT EXECUTE ON [dbo].[sproc_name]

TO [role1], [role2], [role3], [role4], etc...

How to view the names of the sprocs which have grant to a particular rol开发者_运维技巧e and only that particular role exclusively.

For example, i DO NOT want role1 in combination with role2, or role3, i just want to view the ones where only role1 by itself has execute permission.


This is for SQL Server 2000:

SELECT
    OBJECT_NAME(p1.id)
FROM
    syspermissions p1
WHERE
    USER_NAME(p1.grantee) = 'MyRole'
    AND
    OBJECT_NAME(p1.id) = 'MyProc'
    AND
    NOT EXISTS (SELECT *
        FROM
            syspermissions p2
        WHERE
            p1.grantee <> p2.grantee
            AND
            p1.id = p2.id)


Based on the answer by gbn. This is more appropriate, but essentially he provided the answer.

-- show all stored procs where permission is granted to 'MyRole' and only 'MyRole'
SELECT OBJECT_NAME(p1.id) AS sproc_name
  FROM    syspermissions p1
  inner join sysobjects o ON p1.id = o.id
    AND o.xtype = 'p'
    AND o.[name] not like 'dt_%'
WHERE USER_NAME(p1.grantee) = 'MyRole'
AND NOT EXISTS (
        SELECT *        
            FROM  syspermissions p2        
        WHERE p1.grantee <> p2.grantee            
        AND p1.id = p2.id
    )
ORDER BY 1
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜