Eddie Shipman
asked on
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', 0x5CE521831B15A3928B66002D CC774486
--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
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', 0x5CE521831B15A3928B66002D
--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
ASKER
I have no SW user in the syslogins table in my master database but the login DOES exist.
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?
ASKER
nope
ASKER
The user may already exist yet the role and access has not been granted. I need checks for all of those.
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?
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?
ASKER
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', 0x5CE521831B15A3928B66002D CC774486
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
IF NOT EXISTS (select * from dbo.sysxlogins where [name]='SW')
BEGIN
EXEC Master..sp_addlogin 'SW', 'SWPWD', 'MASTER', 'us_english', 0x5CE521831B15A3928B66002D
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
DarthMod - ok to refund by me - Eddie has answered his own question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could do something like:
if (select count(*) from syslogins where name = 'SW' ) = 0
BEGIN
... add your code here ...
END
Cheers!