Don''t create if already exists

• MrBaseball34

  SSCrazy

  Points: 2515

  More actions

  November 14, 2006 at 2:44 pm

  #103270

  Got the script below:

  However, I do not want to create this login and assign access and roles

  if they already exist. How would I modify this script to take this into

  account? Using MSDE, MSSQL 2000 and 2005 Express.

  EXEC Master..sp_addlogin 'SW', 'SWPWD', 'MASTER', 'us_english', 0x5CE521831B15A3928B66002DCC774486

  --Grant access to database SW

  IF EXISTS (select * from dbo.sysdatabases where [name]='SW')

  BEGIN

  EXEC SW..sp_grantdbaccess 'SW', 'SW'

  EXEC SW..sp_addsrvrolemember 'SW', 'sysadmin'

  END

  GO

• Andy B.

  SSC Eights!

  Points: 890

  More actions

  November 15, 2006 at 12:25 am

  #671862

  Same way as you're already doing for granting access...

  IF EXISTS (SELECT 1 FROM dbo.syslogins WHERE name = 'MyLogin')

  EXEC sp_addlogin...

  
  Enjoy!
  
  A Brown
  
  Manage all of your backups in one simple job using this script.

• MrBaseball34

  SSCrazy

  Points: 2515

  More actions

  November 15, 2006 at 7:31 am

  #671946

  I don't have a SW user is syslogins. I also don't know where to find out if/any role has been granted to the table for that login.

  In fact, I don't even HAVE a syslogins table in my master table on SQL2K SP4. I have a sysxlogins

• MrBaseball34

  SSCrazy

  Points: 2515

  More actions

  November 15, 2006 at 8:24 am

  #671973

  Nevermind, I figured it out on my own...

  IF NOT EXISTS (select * from dbo.sysxlogins where [name]='SW')
  BEGIN
    EXEC Master..sp_addlogin 'SW', 'SWPWD', 'MASTER', 'us_english', 0x5CE521831B15A3928B66002DCC774486
  END
  
  IF EXISTS (select * from dbo.sysdatabases where [name]='SW')
  BEGIN
    PRINT REPLICATE('-',70)
  
    IF (select hasdbaccess from SW..sysusers where name = 'SW') = 0
    BEGIN
      PRINT 'Granting Access to SW DB'
      EXEC SW..sp_grantdbaccess 'SW', 'SW'
    END
  
    IF EXISTS (select lgn.name from master.dbo.spt_values spv, 
                     master.dbo.sysxlogins lgn
                     where spv.type = 'SRV' and spv.name = 'sysadmin' and lgn.name = 'SW')
    BEGIN                
      PRINT 'Adding sysadmin role to SW user for SW DB'
      EXEC SW..sp_addsrvrolemember 'SW', 'sysadmin'
    END
  END