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