Grant permission to create objects in a schema?

• Danah

  Old Hand

  Points: 371

  More actions

  November 29, 2012 at 6:47 pm

  #285417

  Hi, I want to give a user permission to create tables, views and procedures, but only in one particular schema.

  Here is what I read (at http://msdn.microsoft.com/en-us/library/ms191291.aspx):

  ALTER

  Confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope. For example, ALTER permission on a schema includes the ability to create, alter, and drop objects from the schema.

  Here is what I tried:

  create login someone with password = 'somepassword'

  go

  create user someone for login someone

  go

  create schema theschema authorization dbo

  go

  grant alter on schema::theschema to someone

  go

  execute as user = 'someone'

  go

  create procedure theschema.p as begin set nocount on end

  Here was the result:

  Msg 262, Level 14, State 1, Procedure p, Line 1

  CREATE PROCEDURE permission denied in database 'thedatabase'.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 29, 2012 at 6:52 pm

  #1564116

  Danah (11/29/2012)

  ---

  Hi, I want to give a user permission to create tables, views and procedures, but only in one particular schema.

  Here is what I read (at http://msdn.microsoft.com/en-us/library/ms191291.aspx):

  ALTER

  Confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope. For example, ALTER permission on a schema includes the ability to create, alter, and drop objects from the schema.

  Here is what I tried:

  create login someone with password = 'somepassword'

  go

  create user someone for login someone

  go

  create schema theschema authorization dbo

  go

  grant alter on schema::theschema to someone

  go

  execute as user = 'someone'

  go

  create procedure theschema.p as begin set nocount on end

  Here was the result:

  Msg 262, Level 14, State 1, Procedure p, Line 1

  CREATE PROCEDURE permission denied in database 'thedatabase'.

  You also need to grant CREATE on the schema.

• Danah

  Old Hand

  Points: 371

  More actions

  November 29, 2012 at 7:02 pm

  #1564119

  Lynn Pettis (11/29/2012)[hr

  You also need to grant CREATE on the schema.

  Hm, the documentation suggests alter gives create permission, but I tried this anyway:

  grant create on schema::theschema to someone

  and got the error "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'create'."

  So from the documentation it looks like control is all permissions, so I tried this:

  grant control on schema::theschema to someone

  And still got the previous error ("Msg 262, Level 14, State 1, Procedure p, Line 1 CREATE PROCEDURE permission denied in database 'thedatabase'.")

  The error says it is occurring on the database, not the schema. But the documentation says alter should let the user create objects in the schema... it doesn't say anything about database permissions. Is the documentation wrong? Seems to be.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 29, 2012 at 7:38 pm

  #1564122

  Okay, it isn't CREATE on the schema. From BOL regarding CREATE PROCEDURE:

  Permissions

  --------------------------------------------------------------------------------

  Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

• Danah

  Old Hand

  Points: 371

  More actions

  November 29, 2012 at 11:25 pm

  #1564155

  Thanks, yeah, that is it.

  It seems a bit odd to grant granular permissions to create objects at the database level, and then grant a blanket alter permission at the schema level. I wonder what the rationale behind that might be.

  In any case, the MSDN page to which I linked is rather misleading as it doesn't mention this anywhere, it states outright that granting alter schema allows the creation of objects in the schema.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 29, 2012 at 11:31 pm

  #1564162

  Danah (11/29/2012)

  ---

  Thanks, yeah, that is it.

  It seems a bit odd to grant granular permissions to create objects at the database level, and then grant a blanket alter permission at the schema level. I wonder what the rationale behind that might be.

  In any case, the MSDN page to which I linked is rather misleading as it doesn't mention this anywhere, it states outright that granting alter schema allows the creation of objects in the schema.

  But the user also has to have the permissions to accomplish the actual commands. If a user is granted the permissions necessary to create a procedure, for instance, but doesn't have the permissions necessary to do it in a particular schema, they can't create a procedure there. Two layers needed to create a procedure.