Last Friday we upgraded our two database servers to MS SQL 2005. Our co-worker Matt Horrell found some scripts online that helped us a lot, like the one that reads all the users and generates another script to create them back on the new server, and the other that reads all the linked servers and do the same.

The whole process took 2:30 hs and it was smoothly done.
I’m posting here the steps to help anybody else.

• SQL: First we ran the scripts and saved the results.
• IIS: created an “Under Construction Site” and set it to all available IP addresses (no host headers)
• IIS: stopped all web sites leaving only the “Under Construction” on
• SQL: detached all the databases on both machines
• SQL: uninstalled SQL 2000 on both machines
• SQL: installed SQL 2005 on both machines
• SQL: ran the script to create all users back on
• SQL: ran the script to link the servers back on
• SQL: attached all the database on both servers
• IIS: restarted all the web sites

As I told before, it took about 2.5 hours total, dealing with around 65 databases.

Here is the script for the users:USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i < = @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
----
IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
----
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
----
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
----
IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
----
OPEN login_curs
----
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
----
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
----
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
----
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
----
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
----
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
----
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
----
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
----
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
----
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
----
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
----
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
----
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
----
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
----
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
----
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
----
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
----
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
----
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
----
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
----
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
----
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
----
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
----
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
----
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
----
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO
Here is the script for the linked servers:--Script to script out all linked/remote servers
--Works on 7.0 and 2000 servers
--remote password decrypt only works on 7.0
----
declare
@status smallint, -- server status
@server sysname, -- server name
@srvid smallint, -- server id
@srvproduct nvarchar(128), -- product name (dflt to ss)
@allsetopt int, --sum of all settable options
@provider nvarchar(128), -- oledb provider name
@datasrc nvarchar(4000), -- oledb datasource property
@location nvarchar(4000), -- oledb location property
@provstr nvarchar(4000), -- oledb provider-string property
@catalog sysname, -- oledb catalog property
@netname varchar(30), -- Server net name
@srvoption varchar(30), -- server options
@loclogin varchar(30), -- Local user
@rmtlogin varchar(30), -- Remote user
@selfstatus smallint, -- linked server login status
@rmtpass varbinary(256), -- linked server login password
@pwdtext nvarchar(128), -- linked server decrypted password
@i int, -- linked server pswd decrypt var
@lsb tinyint, -- linked server pswd decrypt var
@msb tinyint, -- linked server pswd decrypt var
@tmp varbinary(256) -- linked server pswd decrypt var
----
select @allsetopt=number from master.dbo.spt_values
where type = 'A' and name = 'ALL SETTABLE OPTIONS' -- Only 7.0 else use 4063
----
declare d cursor for SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource,
location, providerstring, catalog, srvnetname
from master..sysservers
where srvid > 0 -- Local Server
open d
fetch next from d into @srvid, @status, @server, @srvproduct, @provider, @datasrc,
@location, @provstr, @catalog, @netname
SET NOCOUNT ON
----
while (@@FETCH_STATUS<>-1) begin
----
PRINT '--------------------------------'
Print '-- ' + @server
PRINT '--------------------------------'
If @status in (64,65) --Remote Server
Begin
Print 'sp_addserver'
Print ' @server = '''+ @server + ''''
Print ' GO'
----
If @status = 64
Begin
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = ''rpc'','
Print ' @optvalue = ''false'''
Print ' GO'
End
exec ('declare r cursor for
select l.name, r.remoteusername from
sysremotelogins r join sysservers s on
r.remoteserverid = s.srvid
join syslogins l on
r.sid = l.sid
where s.srvname = '''+ @server + '''')
open r
fetch next from r into @loclogin, @rmtlogin
while (@@FETCH_STATUS<>-1)
begin
Print 'sp_addremotelogin'
Print ' @remoteserver = '''+ @server + ''','
Print ' @loginame = '''+ @loclogin + ''','
Print ' @remotename = '''+ @rmtlogin + ''''
Print ' GO'
fetch next from r into @loclogin, @rmtlogin
end
close r
deallocate r
----
End
Else --Linked server
Begin
If exists (select * from tempdb..sysobjects where name like '#tmpsrvoption%')
Begin
drop table #tmpsrvoption
End
----
Create Table #tmpsrvoption
(
srvoption varchar(30)
)
insert #tmpsrvoption
select v.name
from master.dbo.spt_values v, master.dbo.sysservers s
where srvid = @srvid
and (v.number & s.srvstatus)=v.number
and (v.number & isnull(@allsetopt,4063)) <> 0
and v.number not in (-1, isnull(@allsetopt,4063))
and v.type = 'A'
----
PRINT 'sp_addlinkedserver'
Print ' @server = '''+ @server + ''''
Print ', @srvproduct = ''' + @srvproduct + ''''
If @srvproduct <> 'SQL Server' --Cannot specify additional info for SQL Server Product
Begin
Print ', @provider = ''' + @provider + ''''
Print ', @datasrc = ''' + @datasrc + ''''
Print ', @location = ''' + @location + ''''
Print ', @provstr = ''' + @provstr + ''''
Print ', @catalog = ''' + @catalog + ''''
End
Print ' GO'
----
-- Set all servers options to false, then reset correct server options
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = ''rpc'','
Print ' @optvalue = ''false'''
Print ' GO'
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = ''rpc out'','
Print ' @optvalue = ''false'''
Print ' GO'
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = ''data access'','
Print ' @optvalue = ''false'''
Print ' GO'
----
declare s cursor for SELECT srvoption
from #tmpsrvoption
----
open s
fetch next from s into @srvoption
----
while (@@FETCH_STATUS<>-1)
begin
Print 'sp_serveroption'
Print ' @server = '''+ @server + ''','
Print ' @optname = '''+ @srvoption + ''','
Print ' @optvalue = ''true'''
Print ' GO'
----
fetch next from s into @srvoption
End
close s
deallocate s
----
--Script linked server logins
If exists (select * from tempdb..sysobjects where name like '#tmplink%')
Begin
drop table #tmplink
End
----
create table #tmplink
(
rmtserver sysname,
loclogin sysname null,
selfstatus smallint,
rmtlogin sysname null
)
----
insert #tmplink
exec ('sp_helplinkedsrvlogin '''+ @server + '''')
----
declare ll cursor for
select loclogin, selfstatus, rmtlogin from #tmplink order by rmtlogin
----
open ll
fetch next from ll into @loclogin, @selfstatus, @rmtlogin
----
while (@@FETCH_STATUS<>-1)
begin
-- Use self no remote user/password
If (@selfstatus = 1 and @loclogin is null)
Begin
Print 'sp_addlinkedsrvlogin'
Print ' @rmtsrvname = '''+ @server + ''','
Print ' @useself = ''true'''
Print ' GO'
End
Else
If (@selfstatus = 1 and @loclogin is not null) Begin
Print 'sp_addlinkedsrvlogin'
Print ' @rmtsrvname = '''+ @server + ''','
Print ' @useself = ''true'','
Print ' @locallogin = '''+ @loclogin + ''','
Print ' @rmtuser = NULL,'
Print ' @rmtpassword = NULL'
Print ' GO'
End
Else
If (@selfstatus = 0 and @rmtlogin is null) Begin
Print 'sp_addlinkedsrvlogin'
Print ' @rmtsrvname = '''+ @server + ''','
Print ' @useself = ''false'','
Print ' @locallogin = NULL,'
Print ' @rmtuser = NULL,'
Print ' @rmtpassword = NULL'
Print ' GO'
End
Else
If (@selfstatus = 0) Begin -- Check for Use self mappings
exec ('declare pwd cursor for
select l.password from master..sysservers s
join master..sysxlogins l on s.srvid = l.srvid --where l.sid is not null
where s.srvname = '''+ @server + ''' and l.name = '''+ @rmtlogin + '''')
-- Decrypt passwords
-- Only works for 7.0 server
-- Encrypt algorithm changed in 2000
open pwd
fetch next from pwd into @rmtpass
while @@fetch_status = 0
begin
set @i = 0
set @pwdtext = N''
while @i < datalength(@rmtpass)
begin
set @tmp = encrypt(@pwdtext + nchar(0))
set @lsb = convert(tinyint, substring(@tmp, @i + 1, 1))
^ convert(tinyint, substring(@rmtpass, @i + 1, 1))
set @i = @i + 1
----
set @tmp = encrypt(@pwdtext + nchar(@lsb))
set @msb = convert(tinyint, substring(@tmp, @i + 1, 1))
^ convert(tinyint, substring(@rmtpass, @i + 1, 1))
set @i = @i + 1
----
set @pwdtext = @pwdtext + nchar(convert(smallint, @lsb)
+ 256 * convert(smallint, @msb))
end
----
Print 'sp_addlinkedsrvlogin'
Print ' @rmtsrvname = '''+ @server + ''','
Print ' @useself = ''false'','
If (@loclogin is null)
Begin
Print ' @locallogin = NULL,'
End
Else
Begin
Print ' @locallogin = '''+ @loclogin + ''','
End
If (@rmtlogin is null)
Begin
Print ' @rmtuser = NULL,'
End
Else
Begin
Print ' @rmtuser = '''+ @rmtlogin + ''','
End
If (@pwdtext is null)
Begin
Print ' @rmtpassword = NULL'
End
Else
Begin
print ' @rmtpassword = '''+ @pwdtext + ''''
End
Print ' GO'
----
fetch next from pwd into @rmtpass
end
close pwd
deallocate pwd
End
fetch next from ll into @loclogin, @selfstatus, @rmtlogin
----
End
close ll
deallocate ll
----
----
End
If @netname <> @server -- If the srvnetname.sysservers is different from srvname.sysservers
Begin
Print 'sp_setnetname'
Print ' @server = '''+ @server + ''','
Print ' @network_name = '''+ @netname + ''''
End
----
fetch next from d into @srvid,@status, @server, @srvproduct, @provider, @datasrc,
@location, @provstr, @catalog, @netname
----
End
close d
deallocate d