We have created a schema xyz on a database to enable developers to create and run stored procedures for reporting purposes on a specific database. They have db_datareader rights on the database
The AD group of which the developers are members have the following grants on the schema on that database
GRANT EXECUTE ON SCHEMA::[xyz ] TO [ADGroup] WITH GRANT OPTION
GRANT ALTER ON SCHEMA::[ xyz ] TO [ADGroup]
GRANT VIEW DEFINITION ON SCHEMA::[ xyz ] TO [ADGroup]
GRANT CREATE PROCEDURE TO [xyz ]
Together with grant create procedure rights
The developers can create/alter procedures OK, however when they try to assign the execute permissions on a proceedure they have created to a local SQL service account on the server
Grant execute on SchemaName.SPName to LocalSQLaccount is fails with a message about insufficient permissions.
We don’t want the developers to be able to create or change anything outside of their schema, but do want them to be able to assign execute rights to this service account for any new procedures they create.
As the DBA I can assign execute rights to the procedure OK.
What are the minimum permissions the developer’s AD group need to successfully grant execute to the service account?