:setvar ShadowSourceLogin "ft546_shadow_ro"
:setvar ShadowSourceDatabase "FT546-wcs"

SET NOCOUNT ON;
SET XACT_ABORT ON;

IF DB_ID() <> DB_ID(N'$(ShadowSourceDatabase)')
    THROW 51010, 'Run source permission verification only in ShadowSourceDatabase.', 1;

IF SUSER_ID(N'$(ShadowSourceLogin)') IS NULL
    THROW 51011, 'ShadowSourceLogin does not exist.', 1;

DECLARE @checks TABLE (
    check_name nvarchar(512) NOT NULL,
    expected_value int NOT NULL,
    actual_value int NULL
);

INSERT INTO @checks (check_name, expected_value, actual_value)
SELECT N'server_role_membership_count', 0, COUNT(*)
FROM sys.server_role_members rm
JOIN sys.server_principals member_principal
  ON member_principal.principal_id = rm.member_principal_id
WHERE member_principal.name = N'$(ShadowSourceLogin)';

BEGIN TRY
    EXECUTE AS LOGIN = N'$(ShadowSourceLogin)';

    DECLARE @objects TABLE (
        object_name nvarchar(512) NOT NULL,
        expected_select int NOT NULL,
        is_base_table bit NOT NULL
    );

    INSERT INTO @objects (object_name, expected_select, is_base_table)
    VALUES
        (N'shadow_ro.v_wcs_srgv_queue', 1, 0),
        (N'shadow_ro.v_wcs_srgv_queue_finish', 1, 0),
        (N'shadow_ro.v_device_srgv', 1, 0),
        (N'shadow_ro.v_wcs_srgv_map', 1, 0),
        (N'shadow_ro.v_bas_cargo_space', 1, 0),
        (N'shadow_ro.v_tsj_execution', 1, 0),
        (N'shadow_ro.v_current_database_clock', 1, 0),
        (N'dbo.wcs_srgv_queue', 0, 1),
        (N'dbo.wcs_srgv_queue_finish', 0, 1),
        (N'dbo.device_srgv', 0, 1),
        (N'dbo.wcs_srgv_map', 0, 1),
        (N'dbo.bas_cargo_space', 0, 1),
        (N'dbo.tsj_device_execution_queue', 0, 1);

    INSERT INTO @checks (check_name, expected_value, actual_value)
    SELECT N'select:' + object_name, expected_select,
           HAS_PERMS_BY_NAME(object_name, N'OBJECT', N'SELECT')
    FROM @objects;

    INSERT INTO @checks (check_name, expected_value, actual_value)
    SELECT LOWER(permission_name) + N':' + object_name, 0,
           HAS_PERMS_BY_NAME(object_name, N'OBJECT', permission_name)
    FROM @objects
    CROSS JOIN (VALUES (N'INSERT'), (N'UPDATE'), (N'DELETE'),
                       (N'ALTER'), (N'CONTROL')) AS operations(permission_name)
    WHERE is_base_table = 1;

    INSERT INTO @checks (check_name, expected_value, actual_value)
    SELECT N'database_permission:' + permission_name, 0,
           HAS_PERMS_BY_NAME(DB_NAME(), N'DATABASE', permission_name)
    FROM (VALUES (N'SELECT'), (N'INSERT'), (N'UPDATE'), (N'DELETE'),
                 (N'EXECUTE'), (N'ALTER'), (N'CONTROL'),
                 (N'TAKE OWNERSHIP')) AS permissions(permission_name);

    INSERT INTO @checks (check_name, expected_value, actual_value)
    SELECT N'database_role:' + role_name, 0, IS_MEMBER(role_name)
    FROM (VALUES (N'db_owner'), (N'db_datareader'),
                 (N'db_datawriter'), (N'db_ddladmin'),
                 (N'db_securityadmin'), (N'db_accessadmin'))
         AS roles(role_name);

    INSERT INTO @checks (check_name, expected_value, actual_value)
    SELECT N'forbidden_effective_database_permission_count', 0, COUNT(*)
    FROM sys.fn_my_permissions(NULL, N'DATABASE')
    WHERE permission_name IN (N'SELECT', N'INSERT', N'UPDATE', N'DELETE',
                              N'EXECUTE', N'ALTER', N'CONTROL',
                              N'TAKE OWNERSHIP');

    INSERT INTO @checks (check_name, expected_value, actual_value)
    SELECT N'forbidden_effective_server_permission_count', 0, COUNT(*)
    FROM sys.fn_my_permissions(NULL, N'SERVER')
    WHERE permission_name NOT IN (N'CONNECT SQL', N'VIEW ANY DATABASE');

    INSERT INTO @checks (check_name, expected_value, actual_value)
    SELECT N'forbidden_effective_schema_permission_count', 0, COUNT(*)
    FROM sys.schemas s
    CROSS APPLY sys.fn_my_permissions(QUOTENAME(s.name), N'SCHEMA') p
    WHERE s.name NOT IN (N'sys', N'INFORMATION_SCHEMA')
      AND NOT (s.name = N'shadow_ro' AND p.permission_name = N'SELECT');

    INSERT INTO @checks (check_name, expected_value, actual_value)
    SELECT N'forbidden_effective_object_permission_count', 0, COUNT(*)
    FROM sys.objects o
    JOIN sys.schemas s ON s.schema_id = o.schema_id
    CROSS APPLY sys.fn_my_permissions(
        QUOTENAME(s.name) + N'.' + QUOTENAME(o.name), N'OBJECT') p
    WHERE o.is_ms_shipped = 0
      AND NOT (
          s.name = N'shadow_ro'
          AND o.name IN (N'v_wcs_srgv_queue', N'v_wcs_srgv_queue_finish',
                         N'v_device_srgv', N'v_wcs_srgv_map',
                         N'v_bas_cargo_space', N'v_tsj_execution',
                         N'v_current_database_clock')
          AND p.permission_name = N'SELECT'
      );

    SELECT TOP (0) * FROM shadow_ro.v_wcs_srgv_queue;
    SELECT TOP (0) * FROM shadow_ro.v_wcs_srgv_queue_finish;
    SELECT TOP (0) * FROM shadow_ro.v_device_srgv;
    SELECT TOP (0) * FROM shadow_ro.v_wcs_srgv_map;
    SELECT TOP (0) * FROM shadow_ro.v_bas_cargo_space;
    SELECT TOP (0) * FROM shadow_ro.v_tsj_execution;
    SELECT TOP (0) * FROM shadow_ro.v_current_database_clock;

    REVERT;
END TRY
BEGIN CATCH
    IF ORIGINAL_LOGIN() <> SUSER_SNAME()
        REVERT;
    THROW;
END CATCH;

SELECT check_name, expected_value, actual_value,
       CASE WHEN actual_value = expected_value THEN N'PASS' ELSE N'FAIL' END
           AS status
FROM @checks
ORDER BY check_name;

IF EXISTS (SELECT 1 FROM @checks
           WHERE actual_value IS NULL OR actual_value <> expected_value)
    THROW 51012, 'FT546 shadow source permission verification failed.', 1;