Help Optimize Code

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(4

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

On Thu, 2 Sep 2010 08:26:10 -0700, Joe K.
wrote:

>
>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(4
>
>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)

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(4

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