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