How do I grant a database role execute permissions on a schema? What am I doing wrong?

Question

I am using SQL Server 2008 Express edition.

I have created a Login , User, Role and Schema. I have mapped the user to the login, and assigned the role to the user.

The schema contains a number of tables and stored procedures.

I would like the Role to have execute permissions on the entire schema.

I have tried granting execute permission through management studio and through entering the command in a query window.

GRANT EXEC ON SCHEMA::schema_name TO role_name

But When I connect to the database using SQL management studio (as the login I have created) firstly I cannot see the stored procedures, but more importantly I get a permission denied error when attempting to run them.

The stored procedure in question does nothing except select data from a table within the same schema.

I have tried creating the stored procedure with and without the line:

WITH EXECUTE AS OWNER

This doesn't make any difference.

I suspect that I have made an error when creating my schema, or there is an ownership issue somewhere, but I am really struggling to get something working.

The only way I have successfully managed to execute the stored procedures is by granting control permissions to the role as well as execute, but I don't believe this is the correct, secure way to proceed.

Any suggestions/comments would be really appreciated.

Thanks.

Solution 1

I have had good luck with the following technique below (using a schema named "myschema" for the example). It sounds like you have most of the pieces in place but the schema/role ownership might not be set correctly.

-- Create the role that will own the schema
CREATE ROLE [myschema_owner] AUTHORIZATION [dbo];

-- Create the role that will have execute permissions on the schema
CREATE ROLE [myschema_execute] AUTHORIZATION [dbo];

-- Create the schema owned by our role
CREATE SCHEMA [myschema] AUTHORIZATION [myschema_owner];

-- Assign execute permissions to execute role
GRANT SELECT, EXECUTE ON SCHEMA::[myschema] TO [myschema_execute];

Solution 2

I'm not sure how to limit this to a certain schema (though your syntax looks like it should work) but if it's acceptable to have execute rights on the entire database the following will do the trick, then add your login to the role like Ankur describes.

grant execute to <ROLE_NAME>

Also, granting VIEW DEFINITION to this role will aloow the logins in the role to see the stored procedures.

Comments

[user2864202]

This takes care of adding a login to a role, but not granting execute rights to the role, as was asked by the OP.