How do I grant a database role execute permissions on a schema? What am I doing wrong?

sql stored-procedures permissions schema sql-server-2008-express
Lewray picture Lewray · Dec 13, 2010 · Viewed 7.2k times · Source

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

darwindeeds picture darwindeeds · May 17, 2011

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

Related questions

Is a stored procedure with EXECUTE AS OWNER a valid replacement for a view selecting from third-schema tables?

A database user A should only have access to specific data. This data is currently provided by a view B.VIEW1 selecting from tables owned by schema B and C. CREATE VIEW [B].[VIEW1] AS SELECT * FROM [B].[VIEW2], [C].[…

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More