November 14, 2006 at 2:44 pm
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
November 15, 2006 at 12:25 am
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.
November 15, 2006 at 7:31 am
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
November 15, 2006 at 8:24 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply