Create Role

• scorpianprince

  SSC Eights!

  Points: 908

  More actions

  January 31, 2011 at 12:23 am

  #244388

  Hi guys

  I need to create a new role for some user's for a particular database

  the role is ddladmin

  can you guys help me with the script for that......

  NEVER NEVER EVER GIVE UP;-)

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 31, 2011 at 1:18 am

  #1280276

  db_ddladmin is a built-in role.

  If you are willing to give this role a different permission set, I would create a new one with a different name instead.

  -- Gianluca Sartori

• DSQLServer

  SSChasing Mays

  Points: 647

  More actions

  January 31, 2011 at 1:30 am

  #1280279

  i've never had to create a newly improved version of the already

  built-in role db_ddladmin role, but......

  this is the reason i add users to the db_ddladmin role:

  DDL Admin role Can issue ALL DDL, but cannot issue GRANT, REVOKE, or

  DENY statements. When you don't want to give your developer DB Owner rights

  for security concerns. You can assign this role to your developers along with

  DB Data Reader and Data Writer permission. This role, combined with db_reader

  and db_writer is so close to db_owner, however a lot less dangerous.

• free_mascot

  One Orange Chip

  Points: 27169

  More actions

  January 31, 2011 at 2:37 am

  #1280291

  Check the following link for the detail:

  http://msdn.microsoft.com/en-us/library/ms189612(v=sql.90).aspx

  HTH

  ---------------------------------------------------
  "Thare are only 10 types of people in the world:
  Those who understand binary, and those who don't."

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  January 31, 2011 at 5:54 am

  #1280351

  I would create a role to assign the users to and then assign that role to ddladmin, data_reader, data_writer, and security_admin for the database. That last role is probably the one you're missing. This is the type of role I give to developers in the development system, not production. This way they can create a stored procedure and assign security to that procedure.

  CREATE ROLE [Developer]

  GO

  EXEC sp_addrolemember N'db_securityadmin', N'Developer'

  GO

  EXEC sp_addrolemember N'db_ddladmin',N'Developer'

  GO

  --etc.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning