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?

  • what is @Query (the sql that its trying to execute) ? Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables that are referenced within the EXECUTE string. even if the EXECUTE is trying to read from a view - You have to GRANT SELECT on the VIEW to the user. – Kin May 18 '15 at 18:15
  • 1
    @Kin. everything works fine now with the GRANT SELECT. Many thanks for your help.. – mccb May 18 '15 at 22:03
  • 2
    Deny permissions have precedence over grant permissions, have you an explicit deny on an object you are using in the @query? – Eduardo Pivaral Jul 9 at 17:50

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.