How do I grant privileges to act as a database for a schema? What am I doing wrong?

Question

I am using SQL Server 2008 Express edition.

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

The schema contains several tables and stored procedures.

I would like the role to have execute permissions for the whole schema.

I tried to grant permission to execute through the management studio and by entering a command in the request window.

GRANT EXEC ON SCHEMA::schema_name TO role_name 

But when I connect to the database using the SQL management studio (as the login I created), firstly, I do not see the stored procedures, but, more importantly, I get permission denied when I try to start them.

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

I tried to create a stored procedure with and without a string:

 WITH EXECUTE AS OWNER 

It does not matter.

I suspect that I made a mistake while creating my scheme, or somewhere a problem with property rights, but I'm really trying to do something.

The only way that I successfully performed the stored procedures is to grant access rights to the role, as well as execution, but I do not think that this is the correct, safe way to continue.

Any suggestions / comments would be really appreciated.

Thanks.

Answer 1

There are several problems that I see in your case.

First of all, you will need the View Definition provided for you to be able to see objects in 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 custom dbo scheme.