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
精彩评论