Home Forums SQL Server 2005 Administering granting execute permissions to ALL stored procedure RE: granting execute permissions to ALL stored procedure

  • david.alcock (1/12/2011)


    Okay, I've just had a look..apologies for the lame naming!

    CREATE SCHEMA dba

    CREATE ROLE schema_test

    GRANT EXECUTE ON SCHEMA::dba TO schema_test

    CREATE PROCEDURE dba.Test_Schema

    AS SELECT * FROM dbo.MyTestTable

    When I view the permissions on the stored procedure it doesnt show anything for the role but on the schema the EXECUTE permission for the role shows correctly, this should be cascaded down through to the objects the schema contains.

    I tested earlier on a user added to the role and it appeared to be working.

    That's how I do it.

    If you don't need a fine grain permission set on some procedures, it works.

    On the other hand, you could grant execute on the whole schema and then deny execute on the procedures you want to keep away from users.

    -- Gianluca Sartori