Execute Stored Procedure with Account that has only read access

Question

I created a stored procedure that has TRUNCATE and INSERT statements in it that I use for an SSRS report.

I typically only use an account that is a member of db_datareader for SSRS reports, however this stored procedure will not execute with an account that only has those rights. The following error is reported when attempting to execute the stored procedure:

Cannot find the object "table_name" because it does not exist or you do not have permissions.

Do I need to use an account who has db_datawriter members to execute this SP or is there a way for me to have my db_datareader account execute it?

Answer 1

When I have a situation where someone (AD group or user) needs to TRUNCATE a table or a set of tables, I write the code in a stored procedure using the WITH EXECUTE AS 'dbo' clause, and grant execute rights to this procedure to the AD group/user who is designated to do the job.

For example:

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS 'dbo'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Delete all existing rows from tables as we only need a daily snapshot
    TRUNCATE TABLE dbo.MyTable;
END
GO

GRANT EXECUTE ON [dbo].TruncateMyTable TO [User];

Stored procedures created with the WITH EXECUTE AS dbo clause allow the stored procedure to run under the security context of the server principal who owns the database where the stored procedure resides. This inherently allows the stored procedure to do things like TRUNCATE TABLE that normally wouldn't be possible without db_ddladmin or sysadmin rights. In the example above, the [User] database principal will be allowed to execute the dbo.TruncateMyTable procedure, and thereby truncate the dbo.MyTable table.