Question :
Environment
SQL Server 2012 (11.0.2100.60)
Situation
- user is allowed to execute a number of stored procedure;
- user is readonly denywrite
- 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