Checking to see if a role exists in T_SQL

• John Kress

  SSC-Addicted

  Points: 400

  More actions

  March 16, 2005 at 9:49 am

  #66988

  I'm attempting to add some new roles using a script and then grant the object rights to the role. What I need is a method to determine if the role has been previously created, so that I don't attempt to recreate the role. I'd like to first use something like the following code, to trap for the existing role:

  IF EXISTS (SELECT name FROM sysobjects

  WHERE name = 'TL_ANSWER_T1' AND type = 'TR')

  DROP TRIGGER TL_ANSWER_T1

  This will find a trigger, and drop it if it exists, but I cannot find anything about checking for an existing role.

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 16, 2005 at 10:28 am

  #547315

  declare  @rolename sysname

  set @rolename = 'YourRole'

  if exists (select * from sysusers where name = @rolename and (issqlrole = 1 or isapprole = 1))

   begin

    exec sp_droprole  @rolename

   end

  HTH

  
  * Noel