1

My goal is to write a script which:

  1. Creates a stored procedure
  2. Grants execution permissions to a particular user on that new stored procedure

On this SO question the recommendation is to add GRANT EXECUTE ON at the end of a script that creates a stored procedure.

However, a permissions issue not letting my own non-admin account to grant permissions on stored procedures trying to execute the stored procedure lead me to believe that that GRANT EXECUTE ON statement is executed each time that the stored procedure above it is executed. Am I incorrect?

Edit with clarification: In the script involved, the stored procedure statements included BEGIN...END, and was followed by GO, before the GRANT EXECUTE statement.

1

Take a user created Stored Procedure, MySP and as part of the SQL Script to create the SP add a SQL Statement to Grant execute permissions on the SP.

CREATE Procedure MySP
AS
BEGIN
  SELECT 'HELLO';
END
GO
GRANT EXECUTE ON MySP To SomeUser;
GO

When the SQL is run the SP is created and stored in SQL Server. Once the SP is created the Grant execute statement is executed.

When the SP is executed only the contents of the SP are run.

The Grant execute statement is NOT stored with the SP in SQL Sever. This can be verified in a number of ways

  1. By opening SQL Enterprise Manager, locating the database in which the SP resides and viewing the SP under Programmability.
  2. Use sp_helptext 'MySP' which will return SQL Servers stored version of the SP
  • You are correct. In the stored procedure that made me think that the GRANT EXECUTE was included in the stored proc, there must have been a bug in my code perhaps to do with semicolons or BEGIN...END GO. – edev Mar 4 '16 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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