george sibbald (4/9/2014)
I have not tried this so cannot confirm it it works. Even if it does I can say it would randomize SQL authenticated passwords, so it would be of no use to you if you have any SQL Ids to transfer.I would use sp_help_revlogin, if you google that there is an 2000 to 2005 version on the MS site which would work for you.
If you cannot find it here is the code
--exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
--GO
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
.....
Ok, let me get this straight...
1) Use the above code to output the logins/sids/pwords;
2) Use some other code to restore them onto a 2K5 box
3) Use some other code to output the logins/sids/pwords;
4) Use some other code to restore them onto the final 2K8 box.
Is that about right?
Also, by 'SQL" id's do you mean Database users, or windows logins that are also authenticated by SQL (or both!) ?