RevokeObjectRoleByID.sql

USE [Certificate_Administration]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================================
-- Author:          John Merkel
-- Creation date:   October 20, 2021
-- Description:     Revokes a TargetRole from the SourceObject.
-- Requires:        SA
-- ======================================================================

CREATE OR ALTER PROCEDURE [Permission].[RevokeObjectRoleByID]
      @SourceObjectID   INT
    , @TargetRoleID     INT
    , @DropRequest      BIT
    , @debug            BIT = 1
AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
    BEGIN TRANSACTION

    DECLARE
          @TargetDatabaseID INT
        , @tar_db_id        INT
        , @tar_role_id      INT
        , @tar_cert_id      INT
        , @granted          BIT

    SELECT
          @TargetDatabaseID = [td].[TargetDatabaseID]
        , @tar_db_id        = [td].[database_id]
        , @tar_role_id      = [tr].[role_principal_id]
        , @tar_cert_id      = [toc].[certificate_id]
        , @granted          = [orr].[Granted]
    FROM
        [Permission].[ObjectRoleRequest] AS [orr]
        JOIN [Permission].[TargetRole] AS [tr]
            ON [orr].[TargetRoleID] = [tr].[TargetRoleID]
        JOIN [Permission].[TargetDatabase] AS [td]
            ON [tr].[TargetDatabaseID] = [td].[TargetDatabaseID]
        JOIN [Permission].[SourceObjectCertificate] AS [soc]
            ON [orr].[SourceObjectID] = [soc].[SourceObjectID]
        JOIN [Permission].[TargetObjectCertificate] AS [toc]
            ON [soc].[SourceObjectCertificateID] = [toc].[SourceObjectCertificateID]
    WHERE
        [orr].[SourceObjectID] = @SourceObjectID
        AND [orr].[TargetRoleID] = @TargetRoleID

    IF @tar_db_id IS NULL
        RAISERROR(
            'No object permission request exists with SourceObjectID: "%d" TargetRoleID: "%d"'
            , 16, 1
            , @SourceObjectID, @TargetRoleID
        )

    -- Only hit other databases if the permission has been granted
    IF @granted = 1
    BEGIN

        DECLARE
              @tar_db_name          SYSNAME
            , @tar_role_name        SYSNAME
            , @tar_cert_username    SYSNAME

        -- Get db name
        EXEC [Validation].[DBNameFromID] @tar_db_id, @tar_db_name, 1

        -- Get certificate username
        EXEC [Validation].[CertUsernameFromCertID]
              @tar_db_id
            , @tar_cert_id
            , @tar_cert_username OUTPUT
            , 1
            , @debug

        -- Revoke role
        EXEC [Definition].[DropMemberFromRoleByName]
              @tar_db_name
            , @tar_role_name
            , @tar_cert_username
            , @debug
    END

    IF @DropRequest = 1
        -- Permissions revoked, drop request
        DELETE [orr]
        FROM [Permission].[ObjectRoleRequest] AS [orr]
        WHERE
            [orr].[SourceObjectID] = @SourceObjectID
            AND [orr].[TargetRoleID] = @TargetRoleID
    ELSE IF @granted = 1
        -- Permissions revoked, set Granted = 0
        UPDATE [orr]
        SET [orr].[Granted] = 0
        FROM [Permission].[ObjectRoleRequest] AS [orr]
        WHERE
            [orr].[SourceObjectID] = @SourceObjectID
            AND [orr].[TargetRoleID] = @TargetRoleID

    -- Try to clear any certs that aren't granting permissions
    EXEC [Permission].[TryToDropObjectCertificates] @SourceObjectID, @TargetDatabaseID, @debug

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    ; THROW
END CATCH
GO