Permissions on a Schema

• Roust_m

  SSCoach

  Points: 17410

  More actions

  February 17, 2010 at 10:52 pm

  #220712

  Hi,

  I want to give a user permissions to creata any object within a schema, BUT indexes, as well as read/write/execute, etc.

  Is there a way to do this?

  Thanks.

• chris.rogers00

  Right there with Babe

  Points: 790

  More actions

  February 18, 2010 at 6:36 am

  #1120863

  You can create a role that has the permissions you want and then grant that role to the new user

  e.g. create role NEW_ROLE

  grant select any table to NEW_ROLE

  grant xxx to NEW_ROLE

  Here you have implicitly stated what the role can do. If you don't put that the role can create indexes, then it won't be able to. All you have to do then is grant that role to your new user

  grant NEW_ROLE to NEW_USER

  for a list of roles see

  http://msdn.microsoft.com/en-us/library/ms187965.aspx

  Hope this helps

  Chris

  http://www.eatitnow.co.uk

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 18, 2010 at 6:43 am

  #1120869

  Roust_m (2/17/2010)

  ---

  Hi,

  I want to give a user permissions to creata any object within a schema, BUT indexes, as well as read/write/execute, etc.

  Is there a way to do this?

  Thanks.

  i don't believe you'll be able to give ddl_admin, but also take away the ability to create indexes; for example, if you create a table, any PK or UQ constraints also inherently create indexes.

  If index creation were prevented the effect would be the user would only allow your user to make some very basic HEAP type tables.

  you could use a DDL trigger to (maybe) prevent CREATE INDEX statements, but why? what behavior do you have to prevent? what is "bad" about creating indexes?

  excluding the whole prevent index thing, i would create a role like this, and add users to that role:

  --create the Role for my Dev guys

  CREATE ROLE [WhateverDEVAdmins]

  --create objects

  ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverDEVAdmins]

  --read data from objects

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]

  --insert/update/delete data from objects

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]

  --execute procs/views/functions, ALTER them too

  GRANT EXECUTE,ALTER TO [WhateverDEVAdmins]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Roust_m

  SSCoach

  Points: 17410

  More actions

  February 18, 2010 at 5:56 pm

  #1121494

  Lowell (2/18/2010)

  ---

  Roust_m (2/17/2010)

  ---

  Hi,

  I want to give a user permissions to creata any object within a schema, BUT indexes, as well as read/write/execute, etc.

  Is there a way to do this?

  Thanks.

  i don't believe you'll be able to give ddl_admin, but also take away the ability to create indexes; for example, if you create a table, any PK or UQ constraints also inherently create indexes.

  If index creation were prevented the effect would be the user would only allow your user to make some very basic HEAP type tables.

  you could use a DDL trigger to (maybe) prevent CREATE INDEX statements, but why? what behavior do you have to prevent? what is "bad" about creating indexes?

  excluding the whole prevent index thing, i would create a role like this, and add users to that role:

  --create the Role for my Dev guys

  CREATE ROLE [WhateverDEVAdmins]

  --create objects

  ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverDEVAdmins]

  --read data from objects

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]

  --insert/update/delete data from objects

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]

  --execute procs/views/functions, ALTER them too

  GRANT EXECUTE,ALTER TO [WhateverDEVAdmins]

  I just want to give the release manager the ability to create SPs, may be put new columns into the existing tables, but stop them from creating new indexes, because this has to go through a separate process, so I have a chance to review them.

• Roust_m

  SSCoach

  Points: 17410

  More actions

  February 18, 2010 at 11:44 pm

  #1121575

  It looks like my only option would be DDL trigger...