DB Execute Role Script

• SQLSeTTeR

  SSCertifiable

  Points: 5481

  More actions

  March 16, 2010 at 10:22 am

  #139414

  How can I script this if the role already exists it will not error out, it will skip it? I will still need to sp_addrolemember.

  USE mydb

  GO

  CREATE ROLE db_executor

  GO

  My script:

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

  PRINT '--CREATE EXECUTE ROLE'

  select 'USE ' + name +'

  GO' + '

  CREATE ROLE db_executor' +'

  GO' + '

  USE ' + name + '

  GO ' + '

  EXEC sp_addrolemember N''db_executor'', N''mydomain\mygroup''' +'

  GO'

  FROM sys.databases

  where database_id > 4

• Nik Desai

  SSC Enthusiast

  Points: 135

  More actions

  March 16, 2010 at 12:08 pm

  #1134704

  Here you go.

  --- Create Schema/User

  IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'db_executor' AND type = 'R')

  DROP ROLE [db_executor]

  /* CREATE A NEW ROLE */

  CREATE ROLE db_executor

  Thanks,

  Nikul

• SQLSeTTeR

  SSCertifiable

  Points: 5481

  More actions

  March 16, 2010 at 3:57 pm

  #1134885

  PERFECT!! Got another one if you're not too busy.

  Do you have a script to loop through the users and logins I want to drop and recreate.

• SQLSeTTeR

  SSCertifiable

  Points: 5481

  More actions

  March 16, 2010 at 5:44 pm

  #1134926

  EXEC sp_MSforeachdb 'USE ? IF EXISTS

  (SELECT 1 FROM sysusers WHERE name = ''MYDomain\myGroup'') DROP USER [MYDomain\myGroup]'

  Again, Thanks for your help!