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 schma.

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.

Answer 1

There are couple of issues that I can see in your case.

First of all you would need View Definition granted for you to be able to see the objects in the Management studio.

I would recommend this if you want the role to have all permissions,

GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION
    ON Schema::SchemaName TO [RoleName/LoginName]

Also make sure the owner of your user-defined schema is "dbo".