Grant Execute Permissions on Stored Procedures to a database role or another sql user

• HookSQLDBA7

  SSChampion

  Points: 10709

  More actions

  December 12, 2016 at 12:55 pm

  #315998

  What is the least privilege method to allow a developer the ability to Grant Execute Permissions on Stored Procedures/Views to a database role or another sql user. The REPORTUser_Accounting is the sql login used in our SSRS data source. On our 'play' server, I want to be able to allow developers to Grant Execute and Select Permissions on Stored Procedures/Views that they create to the REPORTUser_Accounting sql login. Therefore, when the SSRS reports are executed they will have access to the appropriate stored procedures/views via the REPORTUser_Accounting sql login used in the SSRS data source.

  While testing this method, I am receiving the following error when I log in as another user (EXECUTE AS LOGIN = 'Developer_User') and attempt to GRANT Execute on the stored procedure to the db role:

  USE [DBName]

  GO

  GRANT EXECUTE ON [SchemaName].[StoredProcedureName] TO [DB Role];

  Msg 15151, Level 16, State 1, Line 1

  Cannot find the object 'stored procedure name', because it does not exist or you do not have permission.

  I have read a lot on the internet regarding this error but have not found a solution. The GRANT EXECUTE works when I grant Control Permission (on Schema) to the developer login or when I put the developer login in the db_securityadmin database role. But, these two methods may be giving up to many permissions.

  I tried the GRANT EXECUTE and WITH GRANT Option at the schema level:

  GRANT EXECUTE ON SCHEMA::[SchamaName] TO [DeveloperLogin] WITH GRANT OPTION

  I must be doing something wrong.

• kevaburg

  SSCoach

  Points: 18131

  More actions

  December 21, 2016 at 6:31 am

  #1919129

  Like this for a single object.....

  grant execute on [schema].[procedure] to user with grant option;

  or like this for all stored procedures.....

  grant execute to user with grant option;

• Thom A

  SSC Guru

  Points: 99137

  More actions

  December 21, 2016 at 9:53 am

  #1919186

  kevaburg (12/21/2016)

  ---

  Like this for a single object.....

  grant execute on [schema].[procedure] to user with grant option;

  or like this for all stored procedures.....

  grant execute to user with grant option;

  The OP posted this elsewhere and I had the same misunderstanding. The OP wants a user to be able to give permissions to a user to GRANT execute permissions to another user. I believe I answered this in the other thread, but the reiterate the permission would be to assign the user with the db_securityadmin role.

  This enables the user to give GRANT/DENY/REVOKE statements on objects, but they themselves cannot access any of the data in the SQL database, and cannot change their own.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• kevaburg

  SSCoach

  Points: 18131

  More actions

  December 21, 2016 at 11:06 am

  #1919200

  Thom A (12/21/2016)

  ---

  kevaburg (12/21/2016)

  ---

  Like this for a single object.....

  grant execute on [schema].[procedure] to user with grant option;

  or like this for all stored procedures.....

  grant execute to user with grant option;

  The OP posted this elsewhere and I had the same misunderstanding. The OP wants a user to be able to give permissions to a user to GRANT execute permissions to another user. I believe I answered this in the other thread, but the reiterate the permission would be to assign the user with the db_securityadmin role.

  This enables the user to give GRANT/DENY/REVOKE statements on objects, but they themselves cannot access any of the data in the SQL database, and cannot change their own.

  OK, now I understand. Yep, db_securityadmin is the best (only) option without having to explicitly define the permissions on individual objects.