sql-serverprivilegessql-grant

Grant with Grant option not working in SQL Server

I have two server logins, OMEGACA and TEST, and an ALL SERVER for LOGON trigger:

CREATE TRIGGER [OMEGACA_ACC]
ON ALL SERVER WITH EXECUTE AS 'OMEGACA'
FOR LOGON
AS
    -- ...............

OMEGACA has server roles public and sysadmin. It is also owner of database OmegaCoreAudit. In this database, I have the OMEGACA schema and the following procedure to disable/enable the OMEGACA_ACC trigger:

USE [OmegaCoreAudit]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [OMEGACA].[P_SYS_MANAGE_ACC] 
    (@p_trigger_status int)
AS
BEGIN
    SET NOCOUNT ON;    

    -- Check Trigger Status
    IF @p_trigger_status = 0
    BEGIN
        DISABLE TRIGGER OMEGACA_ACC ON ALL SERVER;
    END
    ELSE IF @p_trigger_status = 1
    BEGIN
        ENABLE TRIGGER OMEGACA_ACC ON ALL SERVER;
    END
    ELSE
    BEGIN
        RAISERROR('Wrong status 1/0 value !', 16, 1);
    END;
END;

I want user TEST to be able to execute this procedure to enable/disable the server trigger.

I do NOT want to grant this user TEST the "CONTROL SERVER" directly!

So, I:

  1. Grant CONTROL SERVER to OMEGACA login with WITH GRANT option
  2. Map TEST login to OmegaCoreAudit TEST user
  3. Grant EXECUTE ON [OMEGACA].[P_SYS_MANAGE_ACC] to TEST user

Now, when as TEST login I do call:

USE [OmegaCoreAudit]
GO

DECLARE @RC int
-- TODO: Set parameter values here.

EXECUTE @RC = [OMEGACA].[P_SYS_MANAGE_ACC] 0
GO

I get this error:

Msg 1088, Level 16, State 120, Procedure OMEGACA.P_SYS_MANAGE_ACC, Line 21 [Batch Start Line 2]
Cannot find the object "OMEGACA_ACC" because it does not exist or you do not have permissions.

Question

How can I have TEST login execute this procedure correctly?

Solution

  • You're misunderstanding what WITH GRANT OPTION does. That's just to be able to give GRANT to other users/logins. It doesn't allow you to execute on behalf of a user without granting them permission.

    Moreover, the procedure doesn't even have EXECUTE AS OWNER, which is necessary to make the procedure run under the OMEGACA user, who owns the database. Once you add that,

    CREATE OR ALTER PROCEDURE [OMEGACA].[P_SYS_MANAGE_ACC] 
    (@p_trigger_status int)
WITH EXECUTE AS OWNER
AS

    you are still locked into the current database.

    There are three main ways to break out of database-level impersonation to the instance-level.

    I recommend module signing. A full tutorial is here, but the main points are: