I just created a script today since I could not find it anywhere.. it would be gr8 if any of you would go through this and let me know if you think this would correctly transfer the server roles after transfering the logins and passwords (between 2005 instances)
Here is the script:
declare @loginname as varchar(100)
declare @sysadmin as bit
declare @securityadmin as bit
declare @serveradmin as bit
declare @setupadmin as bit
declare @processadmin as bit
declare @diskadmin as bit
declare @dbcreator as bit
declare @bulkadmin as bit
declare @tmpstr as varchar(500)
DECLARE srvrole_curs CURSOR FORWARD_ONLY FOR
select convert(varchar(100),suser_sname(sid)),
sysadmin,
securityadmin,
serveradmin,
setupadmin,
processadmin,
diskadmin,
dbcreator,
bulkadmin
from syslogins
where sysadmin<>0
or securityadmin<>0
or serveradmin<>0
or setupadmin <>0
or processadmin <>0
or diskadmin<>0
or dbcreator<>0
or bulkadmin<>0
OPEN srvrole_curs
FETCH NEXT FROM srvrole_curs INTO @loginname, @sysadmin, @securityadmin,
@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin
while (@@fetch_status = 0)
BEGIN
if @sysadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''sysadmin'''
PRINT @tmpstr
end
if @securityadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''securityadmin'''
PRINT @tmpstr
end
if @serveradmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''serveradmin'''
PRINT @tmpstr
end
if @setupadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''setupadmin'''
PRINT @tmpstr
end
if @processadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''processadmin'''
PRINT @tmpstr
end
if @dbcreator=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''dbcreator'''
PRINT @tmpstr
end
if @diskadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''diskadmin'''
PRINT @tmpstr
end
if @bulkadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
end
FETCH NEXT FROM srvrole_curs INTO @loginname, @sysadmin, @securityadmin,
@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin
end
close srvrole_curs
deallocate srvrole_curs