Security – How to grant a database role execute permissions on a schema? What am I doing wrong

permissionsschemaSecuritysql serverstored-procedures

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.

Best Answer

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];
Related Topic