USE [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

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.

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