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