Grant create proc permission to a user-defined role

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Security (SS2K8) » Grant create proc permission to a...

Grant create proc permission to a user-defined role

Rate Topic

Display Mode

Topic Options

Author

Message

n.heyen

Posted Tuesday, January 01, 2013 1:34 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:31 PM
Points: 85, Visits: 355

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

opc.three

Posted Tuesday, January 01, 2013 2:28 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1401627

n.heyen

Posted Tuesday, January 01, 2013 3:17 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:31 PM
Points: 85, Visits: 355

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

opc.three

Posted Tuesday, January 01, 2013 4:36 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 6,826, Visits: 11,950

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1401631

n.heyen

Posted Tuesday, January 01, 2013 4:51 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 31, 2013 12:31 PM
Points: 85, Visits: 355

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 »

Permissions