InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 12:37:37 GMT20http://www.sqlservercentral.com/Forums/Topic1401623-1526-1.aspxThank 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...NormanTue, 01 Jan 2013 16:51:28 GMTn.heyenhttp://www.sqlservercentral.com/Forums/Topic1401623-1526-1.aspx[quote][b]n.heyen (1/1/2013)[/b][hr]So the ALTER AUTHORIZATION statement made the [Developers] the owners of the schema?[/quote]Exactly.[quote]Then I don't really need to add the rest of the GRANT statements since they are now the owners of their schema.[/quote]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:[code="sql"]GRANT CREATE PROCEDURE TO [Developers]GO[/code]Tue, 01 Jan 2013 16:36:12 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401623-1526-1.aspxSo 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.NormanTue, 01 Jan 2013 15:17:58 GMTn.heyenhttp://www.sqlservercentral.com/Forums/Topic1401623-1526-1.aspxThis 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:[code="sql"]ALTER AUTHORIZATION ON SCHEMA::[Test] TO [Developers][/code]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.Tue, 01 Jan 2013 14:28:29 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1401623-1526-1.aspxFirst 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.[code="other"]USE [master]GOCREATE LOGIN [Heyen] WITH PASSWORD=N'normanheyen', DEFAULT_DATABASE=[AdventureWorks2008R2], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOUSE [AdventureWorks2008R2]GOCREATE SCHEMA [Test] AUTHORIZATION [dbo]GOCREATE USER [Heyen] FOR LOGIN [Heyen] WITH DEFAULT_SCHEMA=[Test]GOCREATE ROLE [Developers] AUTHORIZATION [dbo]GOALTER AUTHORIZATION ON SCHEMA::[Test] TO [Developers]GOEXEC sp_addrolemember N'Developers', N'Heyen'GOEXEC sp_addrolemember N'db_datareader', N'Developers'GOGRANT ALTER ON SCHEMA::[Test] TO [Developers]GOGRANT CONTROL ON SCHEMA::[Test] TO [Developers]GOGRANT DELETE ON SCHEMA::[Test] TO [Developers]GOGRANT EXECUTE ON SCHEMA::[Test] TO [Developers]GOGRANT SELECT ON SCHEMA::[Test] TO [Developers]GOGRANT TAKE OWNERSHIP ON SCHEMA::[Test] TO [Developers]GOGRANT UPDATE ON SCHEMA::[Test] TO [Developers]GOGRANT VIEW DEFINITION ON SCHEMA::[Test] TO [Developers]GOGRANT CREATE PROCEDURE ON SCHEMA::[Test] TO [Developers]GO[/code]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,NormanTue, 01 Jan 2013 13:34:40 GMTn.heyen