[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Don''t create if already exists

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
 
 
0
Eddie Shipman
Asked:
Eddie Shipman
  • 4
  • 4
1 Solution
 
HillwaaaCommented:
Hi EddieShipman,

You could do something like:

if (select count(*) from syslogins where name = 'SW' ) = 0
BEGIN

... add your code here ...

END

Cheers!
0
 
Eddie ShipmanAll-around developerAuthor Commented:
I have no SW user in the syslogins table in my master database but the login DOES exist.
0
 
HillwaaaCommented:
OK, you can also check if they are a database user using the same code structure, but looking in the 'sysusers' table instead of 'syslogins' - does that help?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Eddie ShipmanAll-around developerAuthor Commented:
nope
0
 
Eddie ShipmanAll-around developerAuthor Commented:
The user may already exist yet the role and access has not been granted. I need checks for all of those.
0
 
HillwaaaCommented:
Eddie,

Sorry about the delay on this...

you can manually check these things using
sp_helpuser, sp_helpsrvrole and sp_helpsrvrolemember.

Can you please run

"exec sp_helpuser" and let me know what the row with 'SW' contains?
0
 
Eddie ShipmanAll-around developerAuthor Commented:
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', 'SW'
  END
END
0
 
HillwaaaCommented:
DarthMod - ok to refund by me - Eddie has answered his own question.
0
 
DarthModCommented:
PAQd, 500 points refunded.

DarthMod
CS Moderator
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now