Transfer logins and passwords from SQL Server 2000 to SQL Server 2008

Question

Post reply

Transfer logins and passwords from SQL Server 2000 to SQL Server 2008

WilburSmith

Hall of Fame

Points: 3225
More actions
February 1, 2010 at 1:35 am

#386214

Hi,
how to transfer logins and passwords from SQL Server 2000 to SQL Server 2008?
I've tried the sp_help_revlogin / sp_hexadecimal found in several places in the net but it doesn't seem to function in 2000 ==> 2008. The logins are created but login itself fails after the login creation i.e. the password is not transferred correctly.
Regards, Ville

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

Not tested but it might work, I recommend you to do a test before!!!

There is the link:

http://tinyurl.com/y98ga5w

:hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

WilburSmith Hall of Fame Points: 3225 More actions · Answer 2

Hi,

I tried the script in the middle (no comments visible about the difference in the scripts) but no luck. Password is not transferred....

Regards, ville

CDBA Hall of Fame Points: 3266 More actions · Answer 3

Tried SSIS from 2K5 to 2K8? I mean try till 2K5 using SP and then use SSIS to transfer login?

CDBA

WilburSmith Hall of Fame Points: 3225 More actions · Answer 4

I already tried Transfer Logins task in SSIS but it creates the logins with random passwords according to the online help

regards, ville

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

as I'm just in the process of migrating from sql2000 to sql2008 for my current client I'm using

dbo.sp_help_revlogin_2000_to_2005 , you should also have a proc called sp_hexadecimal and both procs should be in master database.

There may be a 2008 proc version but the migration was originally to go to 2005 but it's been a slow process and it's a few years a go I installed the procs so I can't remember.

But it works seemlessly for me.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 6

I've attached scripts that I use that work well. There is one for SQL Server 2000 and one for 2005/2008.

WilburSmith Hall of Fame Points: 3225 More actions · Answer 7

Hi,

and thanks. These scripts worked. I did not compare what might have been the difference compared to the other versions I've tried.

I tested the

sp_script_login.txt worked fine when I copied a login from Sql2000 server to Sql2008

and

sp_script_login_2005.txt worked fine when I copied a login from Sql2008 server to Sql2008

Regards, Ville

Dugi SSCoach Points: 17998 More actions · Answer 8

hmm maybe another alternative... try Idera free product for this purpose

check: http://is.gd/7KpiI

It is free tool and just try in case!

:w00t::hehe::cool::-P;-)

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

MudLuck SSCrazy Points: 2301 More actions · Answer 9

I don't believe that sp_script_login.txt would work going from 200 to 2008 the reason is that it makes the same call to the sproc sp_hexadecimal that [sp_help_revlogin_2000_to_2005] and sp_help_revlogin make.

The sp_hexadecimal sproc is what returns the password hashed that is what is then insserted into the syslogins table.

My assumption is the encyrption algorthm has changed betweem 2000/2005 to 2008.

So if that is the case the hash you place in the table will decyrpt to a diffrent value and hence not work :angry:

Trying the idera tool now to see what type of tact that took to solve the problem..

Dugi SSCoach Points: 17998 More actions · Answer 10

MudLuck (9/22/2010)
...
Trying the idera tool now to see what type of tact that took to solve the problem..

Let us know if you succeed with Idera tool!?

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

MudLuck SSCrazy Points: 2301 More actions · Answer 11

this is how Idera solves the problem.

--Scripting Server Login (password is null) and Default Database

wothless and it took like an hour to run. In other words it was as slow as syurp running down a wall in the north pool..

all and all I give it a 1.8 out of 4 stars but hell it's free right?

WilburSmith Hall of Fame Points: 3225 More actions · Answer 12

It's a long time ago but I do believe that the scripts (Charles Hottle provided) did really work as I posted here. There were tens of logins and I definately did not have the passwords retyped....

Idera seems to be as useless as SSIS with this particular task

V

shaun.stuart SSCertifiable Points: 7070 More actions · Answer 13

I had to do the same thing - move logins from 2000 to 2008. Ran into some problems and it seemed to me like the issue was caused by SQL Server changing the way it encrypted passwords between versions 2000/2005 and 2008. So the procedures sp_hexadecimal and sp_help_revlogin didn't work for me when going directly from 2000 to 2008. My solution was to migrate the logins to a 2005 server, then move them from there to the 2008 server. I blogged about at http://shaunjstuart.com/archive/2010/08/transferring-logins-from-sql-2000-to-2008.

Shaun

gj_srinivas Valued Member Points: 51 More actions · Answer 14

Dear All,

So what is the final ver here? I am going to start SQL server 2000 to SQL server 2008 R2 upgrade.

Pls suggest the best way to do it ?

Thanks,

-Srinivas