Forum Discussion
Grant with Grant option not working
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."