Forum Discussion

akaraulli's avatar
akaraulli
Copper Contributor
May 10, 2025

Grant with Grant option not working

I have two server logins:
OMEGACA and TEST

... and an ALL SERVER for LOGON trigger, as

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 DB I have the OMEGACA schema and the following procedure to disable/enable the OMEGACA_ACC trigger

USE [OmegaCoreAudit]
GO
/****** Object:  StoredProcedure [OMEGACA].[P_SYS_MANAGE_ACC]    Script Date: 5/10/2025 6:30:00 PM ******/
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 exec 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 do get 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 exec procedure correctly ?

best regards
Altin

1 Reply

Sort By
  • SivertSolem's avatar
    SivertSolem
    Iron Contributor
    May 13, 2025

    The issue you're hitting here, is that [OMEGACA_ACC] is a server level trigger, and is not visible to [TEST], assuming [TEST] only has access to the [OMEGACA] database.

    When the stored procedure [OMEGACA].[P_SYS_MANAGE_ACC] executes, it is still using [TEST]'s permissions.

    You have put the "EXECUTE AS" statement as part of the trigger, meaning when it triggers, it uses [OMEGACA]'s permissions, but it does not grant other accounts ALTER permissions on the trigger.

    I suggest attempting the following, though I expect it not to work:

    CREATE PROCEDURE [OMEGACA].[P_SYS_MANAGE_ACC] ( 
    @p_trigger_status int
    )
WITH EXECUTE AS [OMEGACA]
AS 
BEGIN
...

    My expectations are based on the following information:

    • Applies only to DML statements: SELECT, INSERT, UPDATE, and DELETE.
    • The owners of the calling and the called objects must be the same.
    • Doesn't apply to dynamic queries inside the module

    EXECUTE AS Clause (Transact-SQL) - SQL Server | Microsoft Learn

    As ALTER TRIGGER is not a DML statement, that should not be sufficient.

    Understanding Context Switching | Microsoft Learn has more information, and also writes the following:

    Server-level impersonation can be defined in the following:

    • DDL triggers

    The scope of server-level impersonation is the same as that previously defined in "Explicit Server-Level Context Switching."

    Database-level impersonation can be defined in the following:

    • DML triggers
    • Queues
    • Stored procedures
    • User-defined functions
    • The scope of database-level impersonation is the same as that previously defined in "Explicit Database-Level Context Switching."

Resources