sql-serversql-server-2019

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;