CREATE FUNCTION dbo.ImplyingPermissions (@class NVARCHAR(64),
@permname NVARCHAR(64))
RETURNS @ImplPerms TABLE (permname NVARCHAR(64),
CLASS NVARCHAR(64),
height INT,
RANK INT)
AS
BEGIN
-- http://msdn.microsoft.com/en-us/library/ms177450.aspx
-- The following sample script takes as its arguments the name of a class of a securable and the name of a permission. The sample traverses the permissions hierarchy,
-- from a specified node to the root: CONTROL SERVER on the server. The sample emits the list of permissions that include the specified permission by implication.
/*
-- A. Listing permissions that imply ALTER permission on a schema
SELECT * FROM dbo.ImplyingPermissions('schema', 'alter')
-- B. Listing permissions that imply VIEW DEFINITION permission on an object
SELECT * FROM dbo.ImplyingPermissions('object', 'view definition')
-- C. Listing permissions that imply TAKE OWNERSHIP permission on a route
SELECT * FROM dbo.ImplyingPermissions('route', 'take ownership')
-- D. Listing permissions that imply EXECUTE permission on an XML Schema Collection
SELECT * FROM dbo.ImplyingPermissions('xml schema collection', 'execute')
*/
WITH class_hierarchy(class_desc, parent_class_desc)
AS (SELECT DISTINCT
class_desc,
parent_class_desc
FROM sys.fn_builtin_permissions('')),
PermT(class_desc, permission_name, covering_permission_name, parent_covering_permission_name, parent_class_desc)
AS (SELECT class_desc,
permission_name,
covering_permission_name,
parent_covering_permission_name,
parent_class_desc
FROM sys.fn_builtin_permissions('')),
permission_covers(permission_name, class_desc, LEVEL, inserted_as)
AS (SELECT permission_name,
class_desc,
0,
0
FROM PermT
WHERE permission_name = @permname
AND class_desc = @class
UNION ALL
SELECT covering_permission_name,
class_desc,
0,
1
FROM PermT
WHERE class_desc = @class
AND permission_name = @permname
AND LEN(covering_permission_name) > 0
UNION ALL
SELECT PermT.covering_permission_name,
PermT.class_desc,
permission_covers.LEVEL,
permission_covers.inserted_as + 1
FROM PermT,
permission_covers
WHERE permission_covers.permission_name = PermT.permission_name
AND permission_covers.class_desc = PermT.class_desc
AND LEN(PermT.covering_permission_name) > 0
UNION ALL
SELECT PermT.parent_covering_permission_name,
PermT.parent_class_desc,
permission_covers.LEVEL + 1,
permission_covers.inserted_as + 1
FROM PermT,
permission_covers,
class_hierarchy
WHERE permission_covers.permission_name = PermT.permission_name
AND permission_covers.class_desc = PermT.class_desc
AND permission_covers.class_desc = class_hierarchy.class_desc
AND class_hierarchy.parent_class_desc = PermT.parent_class_desc
AND LEN(PermT.parent_covering_permission_name) > 0)
INSERT @ImplPerms
SELECT DISTINCT
permission_name,
class_desc,
LEVEL,
MAX(inserted_as) AS mia
FROM permission_covers
GROUP BY class_desc,
permission_name,
LEVEL
ORDER BY LEVEL,
mia
RETURN
END