Specific rights to GRANT, REVOKE or DENY on stored procedures

Question

Hi all,

On a SQL Server 2008 database I want to give a user the rights to GRANT, REVOKE and DENY on objects only of type stored procedure (not using the db_securityadmin database role). How can i do this? Thanks!

Sandor

Answer 1

The easiest way would probably be to group the stored procedures into a separate schema and give the desired user CONTROL permission on that schema.

There may be other ways to accomplish what you want, but I'm not aware of them.

Answer 2

Wrap the GRANT/REVOKE/DENY via another stored proc that:

• Has EXECUTE AS OWNER (or user if different schema) to avoid direct rights for the user
• Checks the target object in a stored proc
• Checks the user is allowed or a member of a certain role etc

Otherwise, there is no way to separate permissions per object type