SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grant create proc permission to a user-defined role


Grant create proc permission to a user-defined role

Author
Message
n.heyen
n.heyen
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 464
First off, a Happy New Year 2013 to all. Maybe I'm suffering from the after-effects of last night but I can't figure out what permissions I need to grant to do this right...

We are a small shop and generally don't create procs, we don't do development work, all of our applications are purchased. But one of the report developers wants to try using a proc for a report.

What I think I want to do is create a schema in the database and allow the members of the developer role to create, delete, alter, view definition and execute procs in their schema.

So I created the schema, created the db_role and added users to the db_role.
USE [master]
GO
CREATE LOGIN [Heyen] WITH PASSWORD=N'normanheyen', DEFAULT_DATABASE=[AdventureWorks2008R2], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks2008R2]
GO
CREATE SCHEMA [Test] AUTHORIZATION [dbo]
GO
CREATE USER [Heyen] FOR LOGIN [Heyen] WITH DEFAULT_SCHEMA=[Test]
GO
CREATE ROLE [Developers] AUTHORIZATION [dbo]
GO
ALTER AUTHORIZATION ON SCHEMA::[Test] TO [Developers]
GO
EXEC sp_addrolemember N'Developers', N'Heyen'
GO
EXEC sp_addrolemember N'db_datareader', N'Developers'
GO
GRANT ALTER ON SCHEMA::[Test] TO [Developers]
GO
GRANT CONTROL ON SCHEMA::[Test] TO [Developers]
GO
GRANT DELETE ON SCHEMA::[Test] TO [Developers]
GO
GRANT EXECUTE ON SCHEMA::[Test] TO [Developers]
GO
GRANT SELECT ON SCHEMA::[Test] TO [Developers]
GO
GRANT TAKE OWNERSHIP ON SCHEMA::[Test] TO [Developers]
GO
GRANT UPDATE ON SCHEMA::[Test] TO [Developers]
GO
GRANT VIEW DEFINITION ON SCHEMA::[Test] TO [Developers]
GO
GRANT CREATE PROCEDURE ON SCHEMA::[Test] TO [Developers]
GO



When I run this as SA, it reports that:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

And the final command (GRANT CREATE PROCEDURE) gives a syntax error message.

And of course, the user cannot create a proc in the database, much less not in the Test schema.

So, what should I be doing to get this to work. It seems like it should be fairly common but it isn't exactly clear to me how to get it to work.

Thanks to all,
Norman
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37354 Visits: 14411
This statement made the Developers role the owner of the Test schema. In being the owner, any grants you attempted to do that overlapped the permissions afforded by schema ownership would fail:

ALTER AUTHORIZATION ON SCHEMA::[Test] TO [Developers]



As a side note, CREATE TABLE and CREATE PROC are database-level permissions and therefore cannot be granted at the SCHEMA level. The CRAETE TABLE plus ALTER SCHEMA privileges are required to create tables in a particular schema, although schema-ownership supercedes the need for the explicit ALTER SCHEMA permission.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
n.heyen
n.heyen
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 464
So the ALTER AUTHORIZATION statement made the [Developers] the owners of the schema? Then I don't really need to add the rest of the GRANT statements since they are now the owners of their schema.

Apparently I understand less about security than I thought...

Thanks! I appreciate the explanation.

Norman
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37354 Visits: 14411
n.heyen (1/1/2013)
So the ALTER AUTHORIZATION statement made the [Developers] the owners of the schema?

Exactly.

Then I don't really need to add the rest of the GRANT statements since they are now the owners of their schema.

Almost. You will still need to grant to the Developers role the CREATE PROC permission which is at the database level not the schema level:

GRANT CREATE PROCEDURE TO [Developers]
GO



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
n.heyen
n.heyen
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 464
Thank you, I did figure that missing step out a bit ago. It seems to work as near as I can tell from some limited testing.

I appreciate the advice and help, hopefully if I play with this enough, it will make more sense...

Norman
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search