Help Optimize Code
Joe K.
I have created t-sql script listed below to create t0, t1, t2 ,and t3 user
accounts (first cursor) with there corresponding passwords with only
processadmin server role. Next cursor is used for all user accounts (t0, t1,
t2, t3) have already been created. Need to make sure only processadmin
server role is applied to the user account. It's important that if the user
accounts already is created that they are not created and ensure they have
the only have processadmin server role permission.
Please help me optimize the code listed below.
Thanks so much for the help.
DECLARE @name VARCHAR(256)
DECLARE @SQL VARCHAR(1024)
DECLARE @tmpstr VARCHAR(1024)
DECLARE @tpasswd VARCHAR(48)
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
select 't0' UNION select 't1' UNION select 't2' UNION select 't3'
EXCEPT
SELECT name
FROM sys.syslogins
WHERE name = 't0' OR name = 't1' OR name = 't2' OR name ='t3'
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @name
--
-- Not empty
--
WHILE @@FETCH_STATUS = 0
BEGIN
If (@name= 't0') OR (@name = 't1') OR (@name = 't2') OR (@name ='t3')
BEGIN
if @name = 't1' set @tpasswd = 'Today'
else if @name = ' t2' set @tpasswd = 'Yesterday'
else if @name = ' t3' set @tpasswd = 'To9day'
else if @name = ' t0' set @tpasswd = 'Yest9day'
SET @SQL = 'CREATE LOGIN [' + @name + '] WITH PASSWORD =''
+ @tpasswd + '', DEFAULT_DATABASE=[master];
EXEC (@SQL)
PRINT @SQL
SET @tmpstr = 'master.dbo.sp_addsrvrolemember @loginame= '''
+ @name + ''', @rolename=''processadmin'''
EXEC (@tmpstr)
PRINT (@tmpstr)
END
FETCH NEXT
FROM @getAccountID INTO @name
END
CLOSE @getAccountID
DEALLOCATE @getAccountID
DECLARE @name1 VARCHAR(256)
DECLARE @tmpstr1 VARCHAR(1024)
DECLARE @getAccountID1 CURSOR
SET @getAccountID1 = CURSOR FOR
SELECT name
FROM sys.syslogins
WHERE name IN ('t0','t1','t2','t3')
OPEN @getAccountID1
FETCH NEXT
FROM @getAccountID1 INTO @name1
--
-- Not empty
--
WHILE @@FETCH_STATUS = 0
BEGIN
If (@name1= 't0') OR (@name1 = 't1') OR (@name1 = 't2') OR (@name1 ='t3')
BEGIN
if NOT EXISTS ( select [Member] = m.name
from sys.server_role_members rm
join sys.server_principals r on rm.role_principal_id =
r.principal_id
join sys.server_principals m on
rm.member_principal_id = m.principal_id
where r.name = 'processadmin' and m.name = @name1 )
--
-- Add ProcessAdmin Permission Exclude all other server roles
--
--
SET @tmpstr1 = 'master.dbo.sp_addsrvrolemember @loginame= ''' +
@name1 + ''', @rolename=''processadmin'''
EXEC (@tmpstr1)
PRINT (@tmpstr1)
--
SET @tmpstr1 = 'master.dbo.sp_dropsrvrolemember @loginame= ''' +
@name1 + ''', @rolename=''serveradmin'''
EXEC (@tmpstr1)
PRINT (@tmpstr1)
SET @tmpstr1 = 'master.dbo.sp_dropsrvrolemember @loginame= ''' +
@name1 + ''', @rolename=''sysadmin'''
EXEC (@tmpstr1)
PRINT (@tmpstr1)
SET @tmpstr1 = 'master.dbo.sp_dropsrvrolemember @loginame= ''' +
@name1 + ''', @rolename=''bulkadmin'''
EXEC (@tmpstr1)
PRINT (@tmpstr1)
SET @tmpstr1 = 'master.dbo.sp_dropsrvrolemember @loginame= ''' +
@name1 + ''', @rolename=''dbcreator'''
EXEC (@tmpstr1)
PRINT (@tmpstr1)
SET @tmpstr1 = 'master.dbo.sp_dropsrvrolemember @loginame= ''' +
@name1 + ''', @rolename=''diskadmin'''
EXEC (@tmpstr1)
PRINT (@tmpstr1)
SET @tmpstr1 = 'master.dbo.sp_dropsrvrolemember @loginame= ''' +
@name1 + ''', @rolename=''securityadmin'''
EXEC (@tmpstr1)
PRINT (@tmpstr1)
SET @tmpstr1 = 'master.dbo.sp_dropsrvrolemember @loginame= ''' +
@name1 + ''', @rolename=''setupadmin'''
EXEC (@tmpstr1)
PRINT (@tmpstr1)
END
END
FETCH NEXT
FROM @getAccountID1 INTO @name1
END
CLOSE @getAccountID1
DEALLOCATE @getAccountID1
John Bell
Assuming that you don't have indirect role memberships try this but it
is untested:
DECLARE @name VARCHAR(256)
DECLARE @SQL VARCHAR(1024)
DECLARE @rolename VARCHAR(256)
DECLARE @tpasswd VARCHAR(48)
DECLARE @getAccountID CURSOR
DECLARE @getRole CURSOR
SET @getAccountID = CURSOR FOR
SELECT 't0' UNION ALL SELECT 't1' UNION ALL SELECT 't2' UNION ALL
SELECT 't3'
SET @getRole = CURSOR FOR
SELECT 'serveradmin' UNION ALL SELECT 'sysadmin' UNION ALL SELECT
'bulkadmin' UNION ALL SELECT 'dbcreator' UNION ALL SELECT 'diskadmin'
UNION ALL SELECT 'securityadmin' UNION ALL SELECT 'setupadmin'
OPEN @getAccountID
FETCH NEXT FROM @getAccountID INTO @name
--
-- Not empty
--
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tpasswd = 'Today' + @name ;
SET @SQL = 'CREATE LOGIN [' + @name + '] WITH PASSWORD =''' +
@tpasswd + ''', DEFAULT_DATABASE=[master];' ;
EXEC (@SQL) ;
PRINT @SQL ;
SET @SQL = 'IF NOT EXISTS ( SELECT * FROM sys.server_role_members
rm
JOIN sys.server_principals r ON
rm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON
rm.member_principal_id = m.principal_id
where r.name = ''processadmin'' and
m.name = ''' + @name + ''')
EXEC
master.dbo.sp_addsrvrolemember @loginame= ''' + @name + ''',
@rolename=''processadmin'';' ;
EXEC (@SQL) ;
PRINT @SQL ;
OPEN @getRole
FETCH NEXT FROM @getRole INTO @rolename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'IF EXISTS ( SELECT * FROM
sys.server_role_members rm
JOIN sys.server_principals r ON
rm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON
rm.member_principal_id = m.principal_id
where r.name = ''' + @rolename + '''
and m.name = ''' + @name + ''')
EXEC
master.dbo.sp_dropsrvrolemember @loginame= ''' + @name + ''',
@rolename=''' + @rolename + ''';' ;
EXEC (@SQL) ;
PRINT @SQL ;
FETCH NEXT FROM @getRole INTO @rolename
END
CLOSE @getRole
FETCH NEXT FROM @getAccountID INTO @name
END
DEALLOCATE @getRole
CLOSE @getAccountID
DEALLOCATE @getAccountID
John
Erland Sommarskog
already a member, you don't the IF NOT EXISTS.
Also, no need for the dynamic SQL:
EXEC sp_dropsrvrolemember @name, 'processadmin'
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx