4

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?

Community
  • 1
mccb
  • 41
  • 4

1 Answers1

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

Sushil
  • 9
  • 2