Grant Execute with Grant issue

  • Ok, this is getting a bit irritating... I have a role that I have created to allow our developers to create stored procedures and functions (T-SQL for the role to follow). Everything was working with no issues for a couple of days and now the user associated with the role is running into the following error when granting execute permissions on the newly created procedure to public (being used just for testing).

    [font="Courier New"]Msg 15151, Level 16, State 1, Line 1

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

    [/font]

    Create Role T-SQL:

    IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Developer' AND type = 'R')

    DROP ROLE [Developer]

    GO

    -- Create the database role

    CREATE ROLE [Developer] AUTHORIZATION [db_owner]

    GO

    -- Grant database level permissions to the role

    GRANT ALTER ANY SCHEMA TO [Developer]

    GO

    GRANT CREATE FUNCTION TO [Developer]

    GO

    GRANT CREATE PROCEDURE TO [Developer]

    GO

    GRANT SHOWPLAN TO [Developer]

    GO

    GRANT VIEW DEFINITION TO [Developer]

    GO

    GRANT EXECUTE TO [Developer] WITH GRANT OPTION

    GO

    Stored Procedure T-SQL:

    USE [dba_objects_TEST]

    GO

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_dba_testing]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[usp_dba_testing]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[usp_dba_testing]

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT TOP 20 *

    FROM dbo.DeadlockEvents;

    END

    GO

    GRANT EXECUTE ON [dbo].[usp_dba_testing] TO [public];

    GO

    This was working just fine for a couple of days, but now is not, and as far as I can tell the required bits are in place for this to work properly. Anyone out there have any insight into this or see anything I'm missing? I've tried changing the GRANT EXECUTE... WITH GRANT OPTION to be at the database and also at the schema level and everything is being qualified with the schema names...

    TIA

    Mike

    T. Michael Herring
    Database Administrator

  • Is the login in use still associated with that database role?

    Has there been any Deny command issued on any of the needed rights since the Grant was issued?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yes, the user is still associated with the role, and no, there have been no denies added to the role or user.

    T. Michael Herring
    Database Administrator

  • Did you find a solution? Thanks.

  • When you are able to grant permission, Am sure definitely the users part of developer would have db_owner privileges.

    I think there is limitation for the objects created under dbo schema when we grant execute privilege with 'with grant option' at database level not at object level.

    I tried to create objects under user defined schema and able to grant execute privilege on proc in user defined schema to other user.

    here are my test queries.

    -- log on with login X which has default schema as userdefined_schema

    create proc wgo_p1

    as

    begin

    select @@servername

    end

    grant execute on [userdefined_schema].[wgo_p1] to newuser

    -- sunccessful

    to confirm this I have created one more schema userdefined_schema_wgo with authorization to login/user Y

    create proc userdefined_schema_wgo.wgo_p1

    as

    begin

    select @@servername

    end

    grant execute on [userdefined_schema_wgo].[wgo_p1] to newuser

    -- fails when you execute with login X

    and login X can't grant permission to newuser on proc userdefined_schema_wgo.wgo_p1

    then authorization on schema userdefined_schema_wgo has been changed to X.

    now login X can grant permission to newuser on proc userdefined_schema_wgo.wgo_p1

    pre setup: I have granted all the privileges same as you as below:

    CREATE ROLE [Developer] AUTHORIZATION [db_owner]

    GO

    -- Grant database level permissions to the role

    GRANT ALTER ANY SCHEMA TO [Developer]

    GO

    GRANT CREATE FUNCTION TO [Developer]

    GO

    GRANT CREATE PROCEDURE TO [Developer]

    GO

    GRANT CREATE table TO [Developer]

    go

    GRANT SHOWPLAN TO [Developer]

    GO

    GRANT VIEW DEFINITION TO [Developer]

    GO

    GRANT EXECUTE TO [Developer] WITH GRANT OPTION

    and login X is part of role Developer.

    And thank you very much for the scenario, it helped me to explore most of the schema and with grant option stuff.

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

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