stig.sql

USE tempdb;
                GO

                IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'STIG')
                BEGIN
	                DROP SCHEMA STIG
                END
                GO

                CREATE SCHEMA STIG
                GO

            /*
            Objects defined in this file:
            VIEW STIG.database_role_members
                Based on the system view sys.database_role_members, this presents the list of
                database role memberships using roles' and users' names rather than their id numbers.
                Although membership in database roles is hierarchical, this view lists only the direct memberships.
            FUNCTION STIG.database_roles_of(@database_principal sysname)
                Given the name of a database principal (user or role), this table-valued function returns
                a list of all the roles it belongs to, both directly and indirectly.
            FUNCTION STIG.members_of_db_role(@database_role sysname)
                Given the name of a database role, this table-valued function returns
                a list of all the roles and users that belong to it, both directly and indirectly.
            VIEW STIG.database_permissions
                Based on the system view sys.database_permissions, this provides additional, descriptive material.
                The list includes only those permissions explicitly granted (or denied) to a database user or role;
                it does not include permissions that are implicit or inherited from a higher-level role.
                Securable items that exist but have no explicit permissions assigned are included in the
                list, with the columns that describe the grantor and grantee left null.
            FUNCTION STIG.database_effective_permissions(@Grantee sysname)
                Given the name of a database principal (user or role), this table-valued function
                returns information about permissions granted (or denied) to that user or database role,
                either directly or inherited from a higher-level role.
            VIEW STIG.server_role_members
                Based on the system view sys.server_role_members, this presents the list of
                server role memberships using roles' and users' names rather than their id numbers.
                Although membership in server roles is hierarchical, this view lists only the direct memberships.
            FUNCTION STIG.server_roles_of(@server_principal sysname)
                Given the name of a server principal (login or role), this table-valued function returns
                a list of all the roles it belongs to, both directly and indirectly.
            FUNCTION STIG.members_of_server_role(@server_role sysname)
                Given the name of a server role, this table-valued function returns
                a list of all the roles and logins that belong to it, both directly and indirectly.
            VIEW STIG.server_permissions
                Based on the system view sys.server_permissions, this provides additional, descriptive material.
                The list includes only those permissions explicitly granted (or denied) to a server login or role;
                it does not include permissions that are implicit or inherited from a higher-level role.
                Securable items that exist but have no explicit permissions assigned are included in the
                list, with columns describing the grantor and grantee left null.
            FUNCTION STIG.server_effective_permissions(@Grantee sysname)
                Given the name of a server principal (login or server role), this table-valued function
                returns information about permissions granted (or denied) to that login or role,
                either directly or inherited from a higher-level role.
            */


            BEGIN TRY DROP VIEW STIG.database_role_members END TRY BEGIN CATCH END CATCH;
            GO

            CREATE VIEW STIG.database_role_members
            --  Based on the system view sys.database_role_members, this presents the list of
            --  database role memberships using roles' and users' names rather than their id numbers.
            --  Although membership in database roles is hierarchical, this view lists only the direct memberships.
            AS SELECT
                R.name  AS [Role],
                M.name  AS [Member]
            FROM
                <TARGETDB>.sys.database_role_members X
                INNER JOIN <TARGETDB>.sys.database_principals R ON R.principal_id = X.role_principal_id
                INNER JOIN <TARGETDB>.sys.database_principals M ON M.principal_id = X.member_principal_id
            ;
            GO


            BEGIN TRY DROP FUNCTION STIG.database_roles_of END TRY BEGIN CATCH END CATCH;
            GO

            CREATE FUNCTION STIG.database_roles_of(@database_principal sysname)
            --  Membership in database roles is hierarchical.
            --  Given the name of a database principal (user or role), this table-valued function returns
            --  a list of all the roles it belongs to, both directly and indirectly.
                RETURNS @T TABLE
                    (
                    [Member]            sysname,
                    [Role]              sysname,
                    [via Member]        sysname,
                    [Membership Chain]  nvarchar(max)
                    )
            AS BEGIN;
                WITH Membership AS
                (
                SELECT
                    [Member] AS [Member],
                    [Role],
                    [Member] AS [via Member],
                    CAST([Member] AS varchar(max)) + ' < ' + CAST([Role] AS varchar(max)) AS [Membership Chain]
                FROM
                    STIG.database_role_members
                WHERE
                    [Member] = @database_principal

                UNION ALL

                SELECT
                    X.[Member],
                    R.[Role],
                    R.[Member] AS [via Member],
                    X.[Membership Chain] + ' < ' + CAST(R.[Role] AS varchar(max)) AS [Membership Chain]
                FROM
                    Membership X
                    INNER JOIN STIG.database_role_members R ON X.[Role] = R.[Member]
                )
                INSERT INTO @T SELECT * FROM Membership;
            ExitFunction:
                RETURN;
            END;
            GO


            BEGIN TRY DROP FUNCTION STIG.members_of_db_role END TRY BEGIN CATCH END CATCH;
            GO

            CREATE FUNCTION STIG.members_of_db_role(@database_role sysname)
            --  Membership in database roles is hierarchical.
            --  Given the name of a database role, this table-valued function returns
            --  a list of all the roles and users that belong to it, both directly and indirectly.
                RETURNS @T TABLE
                    (
                    [Role]              sysname,
                    [Member]            sysname,
                    [via Role]          sysname,
                    [Membership Chain]  nvarchar(max)
                    )
            AS BEGIN;
                WITH Membership AS
                (
                SELECT
                    [Role] AS [Role],
                    [Member],
                    [Role] AS [via Role],
                    CAST([Role] AS varchar(max)) + ' > ' + CAST([Member] AS varchar(max)) AS [Membership Chain]
                FROM
                    STIG.database_role_members
                WHERE
                    [Role] = @database_role

                UNION ALL

                SELECT
                    X.[Role] AS [Role],
                    R.[Member],
                    R.[Role] AS [via Role],
                    X.[Membership Chain] + ' > ' + CAST(R.[Member] AS varchar(max)) AS [Membership Chain]
                FROM
                    Membership X
                    INNER JOIN STIG.database_role_members R ON X.[Member] = R.[Role]
                )
                INSERT INTO @T SELECT * FROM Membership;
            ExitFunction:
                RETURN;
            END;
            GO




            BEGIN TRY DROP VIEW STIG.database_permissions END TRY BEGIN CATCH END CATCH;
            GO

            CREATE VIEW STIG.database_permissions
            --  Based on the system view sys.database_permissions, this provides additional, descriptive material.
            --  The list includes only those permissions explicitly granted (or denied) to a database user or role;
            --  it does not include permissions that are implicit or inherited from a higher-level role.
            --  Securable items that exist but have no explicit permissions assigned are included in the
            --  list, with the columns that describe the grantor and grantee left null.
            AS SELECT DISTINCT
                @@SERVERNAME        AS [Current Server],
                @@SERVICENAME       AS [Current Instance],
                '<QUOTETARGETDB>'   AS [Current DB],
                SYSTEM_USER         AS [Current Login],
                USER                AS [Current User],

                CASE
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'OBJECT_OR_COLUMN'             THEN CASE WHEN DP.minor_id > 0 THEN 'COLUMN' ELSE OB.type_desc END
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NOT NULL                      THEN DP.class_desc
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND DB.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'DATABASE'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND OB.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'OBJECT_OR_COLUMN'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SC.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'SCHEMA'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND PR.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'DATABASE_PRINCIPAL'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND AY.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'ASSEMBLY'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND TP.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'TYPE'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND XS.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'XML_SCHEMA_COLLECTION'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND MT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'MESSAGE_TYPE'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND VC.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'SERVICE_CONTRACT'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SV.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'SERVICE'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND RS.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'REMOTE_SERVICE_BINDING'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND RT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'ROUTE'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND FT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'FULLTEXT_CATALOG'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SK.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'SYMMETRIC_KEY'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND AK.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'ASYMMETRIC_KEY'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND CT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'CERTIFICATE'
                    ELSE NULL
                END                 AS [Securable Type or Class],
                CASE
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'DATABASE'                     THEN PS.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'OBJECT_OR_COLUMN'             THEN schema_name(OB.schema_id)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SCHEMA'                       THEN P3.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'DATABASE_PRINCIPAL'           THEN coalesce(PR.default_schema_name, PE.name)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ASSEMBLY'                     THEN P4.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'TYPE'                         THEN schema_name(TP.schema_id)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'XML_SCHEMA_COLLECTION'        THEN schema_name(XS.schema_id)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'MESSAGE_TYPE'                 THEN P5.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SERVICE_CONTRACT'             THEN P6.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SERVICE'                      THEN P7.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'REMOTE_SERVICE_BINDING'       THEN P8.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ROUTE'                        THEN P9.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'FULLTEXT_CATALOG'             THEN PA.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SYMMETRIC_KEY'                THEN PB.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ASYMMETRIC_KEY'               THEN PC.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'CERTIFICATE'                  THEN PD.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND DB.name IS NOT NULL  THEN PS.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND OB.name IS NOT NULL  THEN schema_name(OB.schema_id)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SC.name IS NOT NULL  THEN P3.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND PR.name IS NOT NULL  THEN PR.default_schema_name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND AY.name IS NOT NULL  THEN P4.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND TP.name IS NOT NULL  THEN schema_name(TP.schema_id)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND XS.name IS NOT NULL  THEN schema_name(XS.schema_id)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND MT.name IS NOT NULL  THEN P5.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND VC.name IS NOT NULL  THEN P6.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SV.name IS NOT NULL  THEN P7.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND RS.name IS NOT NULL  THEN P8.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND RT.name IS NOT NULL  THEN P9.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND FT.name IS NOT NULL  THEN PA.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SK.name IS NOT NULL  THEN PB.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND AK.name IS NOT NULL  THEN PC.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND CT.name IS NOT NULL  THEN PD.name COLLATE DATABASE_DEFAULT
                    ELSE NULL
                END                 AS [Schema/Owner],
                CASE
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'DATABASE'                     THEN DB.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'OBJECT_OR_COLUMN'             THEN OB.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SCHEMA'                       THEN SC.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'DATABASE_PRINCIPAL'           THEN PR.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ASSEMBLY'                     THEN AY.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'TYPE'                         THEN TP.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'XML_SCHEMA_COLLECTION'        THEN XS.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'MESSAGE_TYPE'                 THEN cast(MT.name as sql_variant)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SERVICE_CONTRACT'             THEN cast(VC.name as sql_variant)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SERVICE'                      THEN cast(SV.name as sql_variant)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'REMOTE_SERVICE_BINDING'       THEN RS.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ROUTE'                        THEN RT.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'FULLTEXT_CATALOG'             THEN FT.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SYMMETRIC_KEY'                THEN SK.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ASYMMETRIC_KEY'               THEN AK.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'CERTIFICATE'                  THEN CT.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND DB.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN DB.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND OB.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN OB.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SC.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN SC.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND PR.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN PR.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND AY.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN AY.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND TP.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN TP.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND XS.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN XS.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND MT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN cast(MT.name as sql_variant)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND VC.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN cast(VC.name as sql_variant)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SV.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN cast(SV.name as sql_variant)
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND RS.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN RS.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND RT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN RT.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND FT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN FT.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SK.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN SK.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND AK.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN AK.name COLLATE DATABASE_DEFAULT
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND CT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN CT.name COLLATE DATABASE_DEFAULT
                    ELSE NULL
                END                 AS [Securable],
                CM.name             AS [Column],
                P1.type_desc        AS [Grantee Type],
                P1.name             AS [Grantee],
                DP.permission_name  AS [Permission],
                DP.state_desc       AS [State],
                P2.name             AS [Grantor],
                P2.type_desc        AS [Grantor Type],
                CASE
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'DATABASE'                     THEN 'sys.databases'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'OBJECT_OR_COLUMN'             THEN 'sys.all_objects'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SCHEMA'                       THEN 'sys.schemas'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'DATABASE_PRINCIPAL'           THEN 'sys.database_principals'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ASSEMBLY'                     THEN 'sys.assemblies'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'TYPE'                         THEN 'sys.types'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'XML_SCHEMA_COLLECTION'        THEN 'sys.xml_schema_collections'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'MESSAGE_TYPE'                 THEN 'sys.service_message_types'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SERVICE_CONTRACT'             THEN 'sys.database_principals'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SERVICE'                      THEN 'sys.services'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'REMOTE_SERVICE_BINDING'       THEN 'sys.remote_service_bindings'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ROUTE'                        THEN 'sys.routes'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'FULLTEXT_CATALOG'             THEN 'sys.database_principals'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'SYMMETRIC_KEY'                THEN 'sys.symmetric_keys'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'ASYMMETRIC_KEY'               THEN 'sys.asymmetric_keys'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT = 'CERTIFICATE'                  THEN 'sys.certificates'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND DB.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.databases'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND OB.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.all_objects'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SC.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.schemas'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND PR.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.database_principals'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND AY.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.assemblies'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND TP.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.types'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND XS.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.xml_schema_collections'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND MT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.service_message_types'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND VC.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.database_principals'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SV.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.services'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND RS.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.remote_service_bindings'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND RT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.routes'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND FT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.database_principals'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND SK.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.symmetric_keys'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND AK.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.asymmetric_keys'
                    WHEN DP.class_desc COLLATE DATABASE_DEFAULT IS NULL AND CT.name COLLATE DATABASE_DEFAULT IS NOT NULL  THEN 'sys.certificates'
                    ELSE '<QUOTETARGETDB>.sys.database_permissions'
                END                 AS [Source View]
            FROM
                <TARGETDB>.sys.database_permissions DP
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P1
                    ON  P1.principal_id = DP.grantee_principal_id
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P2
                    ON  P2.principal_id = DP.grantor_principal_id

                FULL OUTER JOIN <TARGETDB>.sys.databases DB
                    ON  DB.database_id = db_id(db_name(DP.major_id))
                    AND DP.class_desc = 'DATABASE'
                LEFT OUTER JOIN <TARGETDB>.sys.server_principals PS
                    ON  PS.sid = DB.owner_sid

                FULL OUTER JOIN <TARGETDB>.sys.all_objects OB
                    ON  DP.major_id   = OB.[object_id]
                    AND DP.class_desc = 'OBJECT_OR_COLUMN'
                LEFT OUTER JOIN <TARGETDB>.sys.all_columns CM
                    ON  CM.[object_id] = DP.major_id
                    AND CM.[column_id] = DP.minor_id

                FULL OUTER JOIN <TARGETDB>.sys.schemas SC
                    ON  DP.major_id   = SC.[schema_id]
                    AND DP.class_desc = 'SCHEMA'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P3
                    ON  P3.principal_id = SC.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.database_principals PR
                    ON  DP.major_id   = PR.principal_id
                    AND DP.class_desc = 'DATABASE_PRINCIPAL'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals PE
                    ON  PE.principal_id = PR.owning_principal_id

                FULL OUTER JOIN <TARGETDB>.sys.assemblies AY
                    ON  DP.major_id   = AY.assembly_id
                    AND DP.class_desc = 'ASSEMBLY'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P4
                    ON  P4.principal_id = AY.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.types TP
                    ON  DP.major_id   = TP.user_type_id
                    AND DP.class_desc = 'TYPE'

                FULL OUTER JOIN <TARGETDB>.sys.xml_schema_collections XS
                    ON  DP.major_id   = XS.xml_collection_id
                    AND DP.class_desc = 'XML_SCHEMA_COLLECTION'

                FULL OUTER JOIN <TARGETDB>.sys.service_message_types MT
                    ON  DP.major_id   = MT.message_type_id
                    AND DP.class_desc = 'MESSAGE_TYPE'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P5
                    ON  P5.principal_id = MT.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.service_contracts VC
                    ON  DP.major_id   = VC.service_contract_id
                    AND DP.class_desc = 'SERVICE_CONTRACT'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P6
                    ON  P6.principal_id = VC.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.services SV
                    ON  DP.major_id   = SV.service_id
                    AND DP.class_desc = 'SERVICE'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P7
                    ON  P7.principal_id = SV.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.remote_service_bindings RS
                    ON  DP.major_id   = RS.remote_service_binding_id
                    AND DP.class_desc = 'REMOTE_SERVICE_BINDING'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P8
                    ON  P8.principal_id = RS.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.routes RT
                    ON  DP.major_id   = RT.route_id
                    AND DP.class_desc = 'ROUTE'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals P9
                    ON  P9.principal_id = RT.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.fulltext_catalogs FT
                    ON  DP.major_id   = FT.fulltext_catalog_id
                    AND DP.class_desc = 'FULLTEXT_CATALOG'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals PA
                    ON  PA.principal_id = FT.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.symmetric_keys SK
                    ON  DP.major_id   = SK.symmetric_key_id
                    AND DP.class_desc = 'SYMMETRIC_KEY'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals PB
                    ON  PB.principal_id = SK.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.asymmetric_keys AK
                    ON  DP.major_id   = AK.asymmetric_key_id
                    AND DP.class_desc = 'ASYMMETRIC_KEY'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals PC
                    ON  PC.principal_id = AK.principal_id

                FULL OUTER JOIN <TARGETDB>.sys.certificates CT
                    ON  DP.major_id   = CT.certificate_id
                    AND DP.class_desc = 'CERTIFICATE'
                LEFT OUTER JOIN <TARGETDB>.sys.database_principals PD
                    ON  PD.principal_id = CT.principal_id
            ;
            GO



            BEGIN TRY DROP FUNCTION STIG.database_effective_permissions END TRY BEGIN CATCH END CATCH;
            GO

            CREATE FUNCTION STIG.database_effective_permissions(@Grantee sysname)
            --  Given the name of a database principal (user or role), this table-valued function
            --  returns information about permissions granted (or denied) to that user or database role,
            --  either directly or inherited from a higher-level role.
                RETURNS @T TABLE
                    (
                    [Current Server]            sysname         null,
                    [Current Instance]          sysname         null,
                    [Current DB]                sysname         null,
                    [Current Login]             sysname         null,
                    [Current User]              sysname         null,
                    [Securable Type or Class]   nvarchar(60)    null,
                    [Schema/Owner]              sysname         null,
                    [Securable]                 sql_variant     null,
                    [Column]                    sysname         null,
                    [Effective Grantee]         sysname         null,
                    [Membership Chain]          nvarchar(max)   null,
                    [Direct Grantee]            sysname         null,
                    [Direct Grantee Type]       nvarchar(60)    null,
                    [Permission]                sysname         null,
                    [State]                     nvarchar(60)    null,
                    [Grantor]                   sysname         null,
                    [Grantor Type]              nvarchar(60)    null,
                    [source view]               sysname         null
                    )
            AS BEGIN;
                WITH Targets AS
                (
                SELECT [Role] AS [Principal], [Membership Chain], len([Membership Chain]) AS [Membership Chain Length] FROM STIG.database_roles_of(@Grantee)
                UNION ALL
                SELECT @Grantee AS [Principal], @Grantee AS [Membership Chain], len(@Grantee) AS [Membership Chain Length]
                )
                INSERT INTO @T
                    SELECT TOP 100000000
                        P.[Current Server],
                        P.[Current Instance],
                        P.[Current DB],
                        P.[Current Login],
                        P.[Current User],
                        P.[Securable Type or Class],
                        P.[Schema/Owner],
                        P.[Securable],
                        P.[Column],
                        @Grantee                AS [Effective Grantee],
                        T.[Membership Chain]    AS [Membership Chain],
                        P.[Grantee]             AS [Direct Grantee],
                        P.[Grantee Type]        AS [Direct Grantee Type],
                        P.[Permission],
                        P.[State],
                        P.[Grantor],
                        P.[Grantor Type],
                        P.[Source View]
                    FROM
                        STIG.database_permissions P
                        INNER JOIN Targets T ON T.[Principal] = P.[Grantee]
                    ORDER BY
                        P.[Securable Type or Class],
                        P.[Schema/Owner],
                        P.[Securable],
                        P.[Column],
                        T.[Membership Chain Length]
                    ;
                    UPDATE T
                    SET [State] = [State] + ' (schema denied)'
                    FROM @T T
                    WHERE
                        T.[State] <> 'DENY'
                    AND    T.[Securable Type or Class] <> 'SCHEMA'
                    AND 0 <
                        (
                        SELECT count(*) FROM @T X
                        WHERE
                            X.[Securable Type or Class] = 'SCHEMA'
                        AND X.[Securable]               = T.[Schema/Owner]
                        AND X.[Permission]              = T.[Permission]
                        AND X.[State]                   = 'DENY'
                        )
                    ;
                    UPDATE T
                    SET [State] = [State] + ' (denied)'
                    FROM @T T
                    WHERE
                        T.[State] <> 'DENY'
                    AND 0 <
                        (
                        SELECT count(*) FROM @T X
                        WHERE
                            X.[Securable Type or Class] = T.[Securable Type or Class]
                        AND    X.[Schema/Owner]         = T.[Schema/Owner]
                        AND X.[Securable]               = T.[Securable]
                        AND    (X.[Column] = T.[Column] OR (X.[Column] IS NULL) AND (T.[Column] IS NULL))
                        AND X.[Permission]              = T.[Permission]
                        AND X.[State]                   = 'DENY'
                        )
                    ;
                    UPDATE T
                    SET [State] = [State] + ' (column(s) denied)'
                    FROM @T T
                    WHERE T.[Securable Type or Class] <> 'COLUMN'
                    AND 0 <
                        (
                        SELECT count(*) FROM @T X
                        WHERE
                            X.[Securable Type or Class] = 'COLUMN'
                        AND X.[Schema/Owner]            = T.[Schema/Owner]
                        AND X.[Securable]               = T.[Securable]
                        AND X.[Permission]              = T.[Permission]
                        AND X.[State]                   = 'DENY'
                        )
                    ;
            ExitFunction:
                RETURN;
            END;
            GO



            BEGIN TRY DROP VIEW STIG.server_role_members END TRY BEGIN CATCH END CATCH;
            GO

            CREATE VIEW STIG.server_role_members
            --  Based on the system view sys.server_role_members, this presents the list of
            --  server role memberships using roles' and logins' names rather than their id numbers.
            --  Although membership in server roles is hierarchical, this view lists only the direct memberships.
            AS SELECT
                R.name    AS [Role],
                M.name    AS [Member]
            FROM
                <TARGETDB>.sys.server_role_members X
                INNER JOIN <TARGETDB>.sys.server_principals R ON R.principal_id = X.role_principal_id
                INNER JOIN <TARGETDB>.sys.server_principals M ON M.principal_id = X.member_principal_id
            ;
            GO


            BEGIN TRY DROP FUNCTION STIG.server_roles_of END TRY BEGIN CATCH END CATCH;
            GO

            CREATE FUNCTION STIG.server_roles_of(@server_principal sysname)
            --  Membership in server roles is hierarchical.
            --  Given the name of a server principal (login or role), this table-valued function returns
            --  a list of all the roles it belongs to, both directly and indirectly.
                RETURNS @T TABLE
                    (
                    [Member]            sysname,
                    [Role]              sysname,
                    [via Member]        sysname,
                    [Membership Chain]  nvarchar(max)
                    )
            AS BEGIN;
                WITH Membership AS
                (
                SELECT
                    [Member] AS [Member],
                    [Role],
                    [Member] AS [via Member],
                    CAST([Member] AS varchar(max)) + ' < ' + CAST([Role] AS varchar(max)) AS [Membership Chain]
                FROM
                    STIG.server_role_members
                WHERE
                    [Member] = @server_principal

                UNION ALL

                SELECT
                    X.[Member],
                    R.[Role],
                    R.[Member] AS [via Member],
                    X.[Membership Chain] + ' < ' + CAST(R.[Role] AS varchar(max)) AS [Membership Chain]
                FROM
                    Membership X
                    INNER JOIN STIG.server_role_members R ON X.[Role] = R.[Member]
                )
                INSERT INTO @T SELECT * FROM Membership;
            ExitFunction:
                RETURN;
            END;
            GO


            BEGIN TRY DROP FUNCTION STIG.members_of_server_role END TRY BEGIN CATCH END CATCH;
            GO

            CREATE FUNCTION STIG.members_of_server_role(@server_role sysname)
            --  Membership in server roles is hierarchical.
            --  Given the name of a server role, this table-valued function returns
            --  a list of all the roles and logins that belong to it, both directly and indirectly.
                RETURNS @T TABLE
                    (
                    [Role]              sysname,
                    [Member]            sysname,
                    [via Role]          sysname,
                    [Membership Chain]  nvarchar(max)
                    )
            AS BEGIN;
                WITH Membership AS
                (
                SELECT
                    [Role] AS [Role],
                    [Member],
                    [Role] AS [via Role],
                    CAST([Role] AS varchar(max)) + ' > ' + CAST([Member] AS varchar(max)) AS [Membership Chain]
                FROM
                    STIG.server_role_members
                WHERE
                    [Role] = @server_role

                UNION ALL

                SELECT
                    X.[Role] AS [Role],
                    R.[Member],
                    R.[Role] AS [via Role],
                    X.[Membership Chain] + ' > ' + CAST(R.[Member] AS varchar(max)) AS [Membership Chain]
                FROM
                    Membership X
                    INNER JOIN STIG.server_role_members R ON X.[Member] = R.[Role]
                )
                INSERT INTO @T SELECT * FROM Membership;
            ExitFunction:
                RETURN;
            END;
            GO



            BEGIN TRY DROP VIEW STIG.server_permissions END TRY BEGIN CATCH END CATCH;
            GO

            CREATE VIEW STIG.server_permissions
            --  Based on the system view sys.server_permissions, this provides additional, descriptive material.
            --  The list includes only those permissions explicitly granted (or denied) to a server login or role;
            --  it does not include permissions that are implicit or inherited from a higher-level role.
            --  Securable items that exist but have no explicit permissions assigned are included in the
            --  list, with columns describing the grantor and grantee left null.
            AS SELECT DISTINCT
                @@SERVERNAME          AS [Current Server],
                @@SERVICENAME         AS [Current Instance],
                '<QUOTETARGETDB>'     AS [Current DB],
                SYSTEM_USER           AS [Current Login],
                USER                  AS [Current User],
                CASE
                    WHEN SP.class_desc IS NOT NULL THEN
                        CASE
                            WHEN SP.class_desc = 'SERVER' AND S.is_linked = 0 THEN 'SERVER'
                            WHEN SP.class_desc = 'SERVER' AND S.is_linked = 1 THEN 'SERVER (linked)'
                            ELSE SP.class_desc
                        END
                    WHEN E.name IS NOT NULL THEN 'ENDPOINT'
                    WHEN S.name IS NOT NULL AND S.is_linked = 0 THEN 'SERVER'
                    WHEN S.name IS NOT NULL AND S.is_linked = 1 THEN 'SERVER (linked)'
                    WHEN P.name IS NOT NULL THEN 'SERVER_PRINCIPAL'
                    ELSE '???'
                END                    AS [Securable Class],
                CASE
                    WHEN E.name IS NOT NULL THEN E.name
                    WHEN S.name IS NOT NULL THEN S.name
                    WHEN P.name IS NOT NULL THEN P.name
                    ELSE '???'
                END                    AS [Securable],
                P1.name                AS [Grantee],
                P1.type_desc           AS [Grantee Type],
                SP.permission_name     AS [Permission],
                SP.state_desc          AS [State],
                P2.name                AS [Grantor],
                P2.type_desc           AS [Grantor Type],
                CASE
                    WHEN SP.class_desc = 'SERVER'                       THEN 'sys.servers'
                    WHEN SP.class_desc = 'ENDPOINT'                     THEN 'sys.endpoints'
                    WHEN SP.class_desc = 'SERVER_PRINCIPAL'             THEN 'sys.server_principals'
                    WHEN SP.class_desc IS NULL AND S.name IS NOT NULL   THEN 'sys.servers'
                    WHEN SP.class_desc IS NULL AND E.name IS NOT NULL   THEN 'sys.endpoints'
                    WHEN SP.class_desc IS NULL AND P.name IS NOT NULL   THEN 'sys.server_principals'
                    ELSE 'sys.server_permissions'
                END                 AS [Source View]
            FROM
                <TARGETDB>.sys.server_permissions SP
                INNER JOIN <TARGETDB>.sys.server_principals P1
                    ON P1.principal_id = SP.grantee_principal_id
                INNER JOIN <TARGETDB>.sys.server_principals P2
                    ON P2.principal_id = SP.grantor_principal_id

                FULL OUTER JOIN sys.servers S
                    ON  SP.class_desc = 'SERVER'
                    AND S.server_id = SP.major_id

                FULL OUTER JOIN <TARGETDB>.sys.endpoints E
                    ON  SP.class_desc = 'ENDPOINT'
                    AND E.endpoint_id = SP.major_id

                FULL OUTER JOIN <TARGETDB>.sys.server_principals P
                    ON  SP.class_desc = 'SERVER_PRINCIPAL'
                    AND P.principal_id = SP.major_id
            ;
            GO


            BEGIN TRY DROP FUNCTION STIG.server_effective_permissions END TRY BEGIN CATCH END CATCH;
            GO

            CREATE FUNCTION STIG.server_effective_permissions(@Grantee sysname)
            --  Given the name of a server principal (login or server role), this table-valued function
            --  returns information about permissions granted (or denied) to that login or role,
            --  either directly or inherited from a higher-level role.
                RETURNS @T TABLE
                    (
                    [Current Server]            sysname         null,
                    [Current Instance]          sysname         null,
                    [Current DB]                sysname         null,
                    [Current Login]             sysname         null,
                    [Current User]              sysname         null,
                    [Securable Class]           nvarchar(60)    null,
                    [Securable]                 sql_variant     null,
                    [Effective Grantee]         sysname         null,
                    [Membership Chain]          nvarchar(max)   null,
                    [Direct Grantee]            sysname         null,
                    [Direct Grantee Type]       nvarchar(60)    null,
                    [Permission]                sysname         null,
                    [State]                     nvarchar(60)    null,
                    [Grantor]                   sysname         null,
                    [Grantor Type]              nvarchar(60)    null,
                    [source view]               sysname         null
                    )
            AS BEGIN;
                WITH Targets AS
                (
                SELECT [Role] AS [Principal], [Membership Chain], len([Membership Chain]) AS [Membership Chain Length] FROM STIG.server_roles_of(@Grantee)
                UNION ALL
                SELECT @Grantee AS [Principal], @Grantee AS [Membership Chain], len(@Grantee) AS [Membership Chain Length]
                )
                INSERT INTO @T
                    SELECT TOP 100000000
                        P.[Current Server],
                        P.[Current Instance],
                        P.[Current DB],
                        P.[Current Login],
                        P.[Current User],
                        P.[Securable Class],
                        P.[Securable],
                        @Grantee                AS [Effective Grantee],
                        T.[Membership Chain]    AS [Membership Chain],
                        P.[Grantee]             AS [Direct Grantee],
                        P.[Grantee Type]        AS [Direct Grantee Type],
                        P.[Permission],
                        P.[State],
                        P.[Grantor],
                        P.[Grantor Type],
                        P.[Source View]
                    FROM
                        STIG.server_permissions P
                        INNER JOIN Targets T ON T.[Principal] = P.[Grantee]
                    ORDER BY
                        P.[Securable Class],
                        P.[Securable],
                        T.[Membership Chain Length]
                    ;
                    UPDATE T
                    SET [State] = [State] + ' (denied)'
                    FROM @T T
                    WHERE
                        T.[State] <> 'DENY'
                    AND 0 <
                        (
                        SELECT count(*) FROM @T X
                        WHERE
                            X.[Securable Class] = T.[Securable Class]
                        AND X.[Securable]       = T.[Securable]
                        AND X.[Permission]      = T.[Permission]
                        AND X.[State]           = 'DENY'
                        )
                    ;
            ExitFunction:
                RETURN;
            END;
            GO