Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant create proc permission to a user-defined role Expand / Collapse
Author
Message
Posted Tuesday, January 1, 2013 1:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 88, Visits: 373
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
Post #1401623
Posted Tuesday, January 1, 2013 2:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1401627
Posted Tuesday, January 1, 2013 3:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 88, Visits: 373
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
Post #1401629
Posted Tuesday, January 1, 2013 4:36 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:01 PM
Points: 7,139, Visits: 12,762
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
Post #1401631
Posted Tuesday, January 1, 2013 4:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 88, Visits: 373
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
Post #1401632
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse