MSSQL – GRANT EXECUTE permission inside stored procedure

Posted on

Question :

Environment

SQL Server 2012 (11.0.2100.60)

Situation

  1. user is allowed to execute a number of stored procedure;
  2. user is readonly denywrite
  3. one of stored procedure has an EXECUTE statement;

The Problem
The user can execute all the stored procedure but not the one having the EXECUTE (even if the EXECUTE is trying to read from a view).

CREATE PROCEDURE [dbo].[SP_INJECT_TEST]

@Query varchar(8000)

AS

SET NOCOUNT ON;
EXECUTE(@Query)

GO;

Question
Is there a specific permission for this situation? Should I switch context and impersonate another USER with all the rights inside the database?

Answer :

Try GRANT SELECT on the view and then GRANT EXECUTE on the procedure to the user.
see this

https://msdn.microsoft.com/en-us/library/ms188371.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *