How to use EXECUTE AS in a procedure without giving a USER explicit IMPERSONATE permissions?

I have a stored procedure in SQL Server 2019. It truncates a staging table and then inserts data. I have granted User1 EXECUTE permissions for this stored procedure usp_insert_data.

However, when I execute the stored procedure, it fails because of the TRUNCATE. If I remove the TRUNCATE, it works OK. My understanding is that TRUNCATE can only be made by users with specific privileges, which I do not want to grant to User1.

I have created another user PowerUser, which I have given ALTER permissions to the table tbl_data, and then included the following code in my stored procedure:

EXECUTE AS USER = N'PowerUser';

TRUNCATE TABLE [dbo].[tbl_data];

REVERT;

My stored procedure now looks like this:

ALTER PROCEDURE [dbo].[usp_insert_data]
    @id bigint,
    @utc datetime,
    @latitude decimal(19,15),
    @longitude decimal(19,15)
AS
    SET NOCOUNT ON;
BEGIN
    EXECUTE AS USER = N'PowerUser';

    TRUNCATE TABLE [dbo].[tbl_data];

    REVERT;

    INSERT INTO [tbl_data] (plot_id, utc, gmt,
                            latitude, longitude)
    VALUES (@plot_id, @utc,
            @utc AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time',
            @latitude, @longitude)
END;

But I still get the following error:

Msg 1088, Level 16, State 7, Procedure dbo.usp_insert_data, Line 7 [Batch Start Line 2]
Cannot find the object "tbl_data" because it does not exist or you do not have permissions.

To allow EXECUTE AS to PowerUser by User1, do I need to run this code:

GRANT IMPERSONATE ON USER:: [PowerUser] TO [User1]

which would mean the user could impersonate at will or am I doing something wrong?

Thanks

Solution

To use an EXECUTE AS statement within a stored procedure the USER/LOGIN needs explicit permission to impersonate that USER/LOGIN. Instead what you should be doing is defining the EXECUTE AS within the WITH clause of the procedure and defining the PowerUser USER in that clause. For example:

--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;

If you want to revert to the calling user within the procedure, you can use 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;