Как использовать EXECUTE AS в процедуре, не предоставляя пользователю явных разрешений IMPERSONATE?

Чтобы использовать оператор EXECUTE AS в хранимой процедуре, USER/LOGIN требуется явное разрешение на олицетворение этого USER/LOGIN. Вместо этого вам следует определить EXECUTE AS в предложении WITH процедуры и определить PowerUserUSER в этом предложении. Например:

--Create a sample table
CREATE TABLE dbo.SomeTable (SomeID int IDENTITY CONSTRAINT PK_SomeTable PRIMARY KEY,
                            SomeString varchar(10),
                            SomeDate date,
                            SomeInt int,
                            TheUser sysname DEFAULT USER_NAME());
GO
--Create the sample user and a power user
CREATE USER SomeUser WITHOUT LOGIN;
CREATE USER PowerUser WITHOUT LOGIN;
GO
GRANT ALTER, INSERT ON dbo.SomeTable TO PowerUser; --Give the PowerUser the ability to ALTER and INSERT the sample table
GO
--Create the sample procedure that TRUNCATEs and the INSERTs into the table
CREATE OR ALTER PROC dbo.SomeProc @SomeString varchar(10),
                                  @SomeDate date,
                                  @SomeInt int
WITH EXECUTE AS 'PowerUser' AS --now the proc will execute as the PowerUser                          
BEGIN

    SET NOCOUNT ON;

    TRUNCATE TABLE dbo.SomeTable;

    INSERT INTO dbo.SomeTable (SomeString, SomeDate, SomeInt)
    VALUES(@SomeString, @SomeDate, @SomeInt);
END;
GO
--Give the sample user permission to execute the proc
GRANT EXECUTE ON dbo.SomeProc TO SomeUser;
GO
--Impersonate our sample user
EXECUTE AS USER = N'SomeUser';
GO
--Run the proc
EXEC dbo.SomeProc 'abc', '20240621','123';
GO
--Run the proc again (this'll delete the original row) 
EXEC dbo.SomeProc 'def', '20240622','456';
GO
--Back to "us"
REVERT;
GO
--Check the data is the second data set, but will have an ID of 1, not 2.
SELECT *
FROM dbo.SomeTable;

GO
--Clean up
DROP USER SomeUser;
DROP PROC dbo.SomeProc;
DROP TABLE dbo.SomeTable;

Если вы хотите вернуться к вызывающему пользователю в рамках процедуры, вы можете использовать EXECUTE AS CALLER:

CREATE OR ALTER PROC dbo.SomeProc @SomeString varchar(10),
                                  @SomeDate date,
                                  @SomeInt int
WITH EXECUTE AS 'PowerUser' AS --now the proc will execute as the PowerUser                          
BEGIN

    SET NOCOUNT ON;

    TRUNCATE TABLE dbo.SomeTable;

    EXECUTE AS CALLER; --Now we're whoever ran the EXEC

    INSERT INTO dbo.SomeTable (SomeString, SomeDate, SomeInt)
    VALUES(@SomeString, @SomeDate, @SomeInt);
    
    REVERT; --Isn't really needed, but to be "safe"
END;