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.