MSSQL - GRANT EXECUTE permission inside stored procedure

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 1

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