Transfer Logins from SQL2000 to SQL2005

Question

Post reply

Transfer Logins from SQL2000 to SQL2005

Ryan-209402

Hall of Fame

Points: 3707
More actions
April 6, 2006 at 9:27 am

#111619

There is an article on how to transfer logins from SQL2000 to SQL2000 (http://support.microsoft.com/default.aspx?kbid=246133) but I have been unable to find MS documentation specifically addressing how to do this between a SQL2000 server and a SQL2005 server. I need to generate a script to create the logins on the target server because the source server will not be available (i.e. the DTS method will not work). Anybody have experience with using sp_help_revlogin on SQL 2005? Thanks.
April 10, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

NEO-159827 SSC Enthusiast Points: 124 More actions · Answer 1

Try the following approaches

You can use many approaches:

- Copy Database Wizard

- A SQL 2000 DTS package

- A SQL 2005 SSIS package

You can use transfer logins task in SSIS, it's also available in sql2k.

OR

I have used this script to move logins with password intact between SQL 2000's. I used it from SQL 2000 to SQL 2005 and appears to work. Run it once on SQL 2000 box, then on the SQL 2005 box create empty dbs with the same name of the default dbs from the ids. then run the create logins output script.

SET NOCOUNT ON

SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''

,', @defdb = ''' + dbname + ''''

,', @deflanguage = ''' + language + ''''

,', @encryptopt = ''skip_encryption'''

,', @passwd ='

, cast(password AS varbinary(256))

,', @sid ='

, sid

FROM syslogins

WHERE name NOT IN ('sa')

AND isntname = 0

Regards,
NEO

forsqlserver SSCoach Points: 18902 More actions · Answer 2

forsqlserver

SSCoach

Points: 18902

June 4, 2009 at 3:14 am

#1004478

Can it be possible using GUI only...

Thanks

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

On the same KB article check method 2 .....

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

I don't know of a way of doing it with the GUI, but if you'll read the article that you referred to in your post, you will find all you need to transfer logins between different versions of SQL Server (including passing logins from SQL Server 2000 to SQL Server 2005).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Dear I have copy paste the script and I have got the logins yet I have not found the passwords in created logins

Thanks

EdVassie SSC Guru Points: 60445 More actions · Answer 6

See my answer at http://www.sqlservercentral.com/Forums/Topic728579-146-1.aspx? for a script that will do this for you.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara