How do I grant a database role execute permissions on a schema? What am I doing wrong?
- by Lewray
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.
