December 12, 2016 at 12:55 pm
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.
December 21, 2016 at 6:31 am
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;
December 21, 2016 at 9:53 am
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
December 21, 2016 at 11:06 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply