Post reply

Grant execute with grant option to a role not working

  • June 5, 2019 at 5:12 pm

    #3650520

    I want my developers to be able to do their jobs with a bare minimum of permissions. They should be able to see all db-objects,  read/write data, create tables, views and procedures as well as granting others execute permission on the procs they create. I have managed to do all of this, except for the permission to grant execute on newly created procedures to others.

    Below is a case describing what I have done. I have created a role which gets execute with grant option on all procedures in the database. Members of this role is able to execute all procedures, but they are not able to pass the permission on.

    GRANT EXECUTE TO [ROLE] WITH GRANT OPTION

    Msg 15151, Level 16, State 1, Line 33

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

     

  • June 5, 2019 at 6:35 pm

    #3650537

    After running below it worked. Any comments on this?

    GRANT CONTROL ON SCHEMA::dbo TO [DBROLE];

    GO

     

  • June 5, 2019 at 7:16 pm

    #3650548

    The problem could be related to this note in documentation:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-2017#with-grant-option

    since you gave "GRANT EXECUTE" permission to a role, you may have needed "AS dbo" clause.  Since you granted CONTROL to the role later, CONTROL permission allows the members of the role to do any GRANT on that schema.

  • June 5, 2019 at 7:49 pm

    #3650566

    Using the with grant option works differently when it's done at the object level vs database/schema level. You would need to use the AS clause and specify the role as Chris noted above but it won't work when the permission for the role is at the database (or schema) level. It does work when the execute with grant option is at the object level. There is a pretty good post up here where they walk through that scenario. It still works as described in that post on SQL Server 2014 - I didn't test it on higher versionsl:

    Grant Execute with Grant issue

    In terms of the change when you granted control on the schema, when granted the CONTROL permission on a schema the role or user can grant permissions on any object in the schema. It's a higher level permission.

    Sue

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply