Sql-server – MSSQL – GRANT EXECUTE permission inside stored procedure

dynamic-sqlpermissionssql server

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?

Best 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

Related Question