PermissionRequests.sql

USE [Certificate_Administration]
GO

CREATE OR ALTER VIEW [Permission].[PermissionRequests] WITH SCHEMABINDING
AS
WITH [Groups] AS (
    SELECT
          'ROLE'                    AS [RequestType]
        , [grr].[SourceGroupID]     AS [SourceGroupID]
        , [grr].[TargetRoleID]      AS [TargetRoleID]
        , [tr].[role_principal_id]  AS [tar_role_id]
        , NULL                      AS [TargetObjectID]
        , NULL                      AS [tar_obj_id]
        , NULL                      AS [GrantablePermissionID]
        , [grr].[Granted]           AS [Granted]
        , [tr].[TargetDatabaseID]   AS [TargetDatabaseID]
    FROM
        [Permission].[GroupRoleRequest] AS [grr]
        JOIN [Permission].[TargetRole] AS [tr]
            ON [grr].[TargetRoleID] = [tr].[TargetRoleID]
    UNION
    SELECT
          'PERMISSION'                   AS [RequestType]
        , [gpr].[SourceGroupID]          AS [SourceGroupID]
        , NULL                           AS [TargetRoleID]
        , NULL                           AS [tar_role_id]
        , [to].[TargetObjectID]          AS [TargetObjectID]
        , [to].[object_id]               AS [tar_obj_id]
        , [gpr].[GrantablePermissionID]  AS [GrantablePermissionID]
        , [gpr].[Granted]                AS [Granted]
        , [to].[TargetDatabaseID]        AS [TargetDatabaseID]
    FROM
        [Permission].[GroupPermissionRequest] AS [gpr]
        JOIN [Permission].[TargetObject] AS [to]
            ON [gpr].[TargetObjectID] = [to].[TargetObjectID]
)
, [Objects] AS (
    SELECT
          'ROLE'                    AS [RequestType]
        , [orr].[SourceObjectID]    AS [SourceObjectID]
        , [orr].[TargetRoleID]      AS [TargetRoleID]
        , [tr].[role_principal_id]  AS [tar_role_id]
        , NULL                      AS [TargetObjectID]
        , NULL                      AS [tar_obj_id]
        , NULL                      AS [GrantablePermissionID]
        , [orr].[Granted]           AS [Granted]
        , [tr].[TargetDatabaseID]   AS [TargetDatabaseID]
    FROM
        [Permission].[ObjectRoleRequest] AS [orr]
        JOIN [Permission].[TargetRole] AS [tr]
            ON [orr].[TargetRoleID] = [tr].[TargetRoleID]
    UNION
    SELECT
          'PERMISSION'                   AS [RequestType]
        , [opr].[SourceObjectID]         AS [SourceObjectID]
        , NULL                           AS [TargetRoleID]
        , NULL                           AS [tar_role_id]
        , [to].[TargetObjectID]          AS [TargetObjectID]
        , [to].[object_id]               AS [tar_obj_id]
        , [opr].[GrantablePermissionID]  AS [GrantablePermissionID]
        , [opr].[Granted]                AS [Granted]
        , [to].[TargetDatabaseID]        AS [TargetDatabaseID]
    FROM
        [Permission].[ObjectPermissionRequest] AS [opr]
        JOIN [Permission].[TargetObject] AS [to]
            ON [opr].[TargetObjectID] = [to].[TargetObjectID]
        
)
, [Requests] AS (
    SELECT
          'GROUP'                       AS [SourceType]
        , [Groups].[RequestType]        AS [RequestType]
        , [sg].[SourceDatabaseID]       AS [SourceDatabaseID]
        , [Groups].[TargetDatabaseID]   AS [TargetDatabaseID]
        , [sg].[SourceGroupID]          AS [SourceGroupID]
        , [sg].[Name]                   AS [GroupName]
        , NULL                          AS [SourceObjectID]
        , NULL                          AS [src_obj_id]
        , [Groups].[TargetRoleID]       AS [TargetRoleID]
        , [Groups].[tar_role_id]        AS [tar_role_id]
        , [Groups].[TargetObjectID]     AS [TargetObjectID]
        , [Groups].[tar_obj_id]         AS [tar_obj_id]
        , [Groups].[GrantablePermissionID]
        , [Groups].[Granted]            AS [Granted]
        , [sgc].[SourceGroupCertificateID] AS [SourceCertificateID]
        , [sgc].[certificate_id]        AS [src_cert_id]
        , [tgc].[SourceGroupCertificateID] AS [TargetCertificateID]
        , [tgc].[certificate_id]        AS [tar_cert_id]
    FROM
        [Groups]
        JOIN [Permission].[SourceGroup] AS [sg]
            ON [Groups].[SourceGroupID] = [sg].[SourceGroupID]
        LEFT JOIN [Permission].[SourceGroupCertificate] AS [sgc]
            ON [Groups].[SourceGroupID] = [sgc].[SourceGroupID]
        LEFT JOIN [Permission].[TargetGroupCertificate] AS [tgc]
            ON [sgc].[SourceGroupCertificateID] = [tgc].[SourceGroupCertificateID]
            AND [Groups].[TargetDatabaseID] = [tgc].[TargetDatabaseID]
    UNION
    SELECT
          'OBJECT'                      AS [SourceType]
        , [Objects].[RequestType]       AS [RequestType]
        , [so].[SourceDatabaseID]       AS [SourceDatabaseID]
        , [Objects].[TargetDatabaseID]  AS [TargetDatabaseID]
        , NULL                          AS [SourceGroupID]
        , NULL                          AS [GroupName]
        , [so].[SourceObjectID]         AS [SourceObjectID]
        , [so].[object_id]              AS [src_obj_id]
        , [Objects].[TargetRoleID]      AS [TargetRoleID]
        , [Objects].[tar_role_id]       AS [tar_role_id]
        , [Objects].[TargetObjectID]    AS [TargetObjectID]
        , [Objects].[tar_obj_id]        AS [tar_obj_id]
        , [Objects].[GrantablePermissionID]
        , [Objects].[Granted]           AS [Granted]
        , [soc].[SourceObjectCertificateID] AS [SourceCertificateID]
        , [soc].[certificate_id]        AS [src_cert_id]
        , [toc].[SourceObjectCertificateID] AS [TargetCertificateID]
        , [toc].[certificate_id]        AS [tar_cert_id]
    FROM
        [Objects]
        JOIN [Permission].[SourceObject] AS [so]
            ON [Objects].[SourceObjectID] = [so].[SourceObjectID]
        LEFT JOIN [Permission].[SourceObjectCertificate] AS [soc]
            ON [Objects].[SourceObjectID] = [soc].[SourceObjectID]
        LEFT JOIN [Permission].[TargetObjectCertificate] AS [toc]
            ON [soc].[SourceObjectCertificateID] = [toc].[SourceObjectCertificateID]
            AND [Objects].[TargetDatabaseID] = [toc].[TargetDatabaseID]
)
SELECT
      [Requests].[SourceType]
    , [Requests].[RequestType]
    , [Requests].[SourceGroupID]
    , [Requests].[GroupName]
    , [Requests].[SourceObjectID]
    , [Requests].[src_obj_id]
    , OBJECT_NAME([Requests].[src_obj_id], [sd].[database_id]) AS [src_obj_name]
    , [Requests].[TargetRoleID]
    , [Requests].[tar_role_id]
    , [Requests].[TargetObjectID]
    , [Requests].[tar_obj_id]
    , OBJECT_NAME([Requests].[tar_obj_id], [td].[database_id]) AS [tar_obj_name]
    , [gp].[GrantablePermissionID]
    , [gp].[Permission]
    , [Requests].[Granted]
    , [sd].[SourceDatabaseID]
    , [sd].[database_id] AS [src_db_id]
    , DB_NAME([sd].[database_id]) AS [src_db_name]
    , [td].[TargetDatabaseID]
    , [td].[database_id] AS [tar_db_id]
    , DB_NAME([td].[database_id]) AS [tar_db_name]
    , [Requests].[SourceCertificateID]
    , [Requests].[src_cert_id]
    , [Requests].[TargetCertificateID]
    , [Requests].[tar_cert_id]
FROM
    [Requests]
    JOIN [Permission].[SourceDatabase] AS [sd]
        ON [Requests].[SourceDatabaseID] = [sd].[SourceDatabaseID]
    JOIN [Permission].[TargetDatabase] AS [td]
        ON [Requests].[TargetDatabaseID] = [td].[TargetDatabaseID]
    LEFT JOIN [Permission].[GrantablePermission] AS [gp]
        ON [Requests].[GrantablePermissionID] = [gp].[GrantablePermissionID]
GO