locked
How to transfer logins and passwords between instances of SQL Server (http://support.microsoft.com/kb/246133) RRS feed

  • Question

  • Thank you in advance for your help with this question.

    We have to move SQL 2000 and existing db to a replacement server.  I am following http://support.microsoft.com/kb/246133 to transfer logins.  I need to confirm our ERP application can access the SQL 2000 db on the replacement server before shutting down the existing server.  Will running the sp_help_revlogin stored procedure on the existing server to extract logins delete the logins on the existing server after exporting them?

    I need to confirm the ERP application will run on the replacement server, so I do not want to delete the logins on the existing server.  I need the existing server to continue functioning until I can confirm the replacement server is working.

    Thank you again.


    User172

    Sunday, June 29, 2014 11:04 PM

Answers

  • No it wont delete anything from your current server, just it will gives you all the logins with SID's and more details to transfer to new server...

    What does the sp_help_revlogin do?

    The sp_help_revlogin SP is a Microsoft provided utility that generates a TSQL script to migrate logins from one server to another. This SP will not only copy the existing logins, but it will also copy the passwords and Security Identification Numbers (SID) associated with SQL Server Authenticated users.


    Raju Rasagounder Sr MSSQL DBA


    • Edited by RAJU RG Sunday, June 29, 2014 11:38 PM
    • Proposed as answer by Ashwin Menon Monday, June 30, 2014 9:29 AM
    • Marked as answer by User172 Monday, June 30, 2014 1:20 PM
    Sunday, June 29, 2014 11:34 PM

All replies

  • No it wont delete anything from your current server, just it will gives you all the logins with SID's and more details to transfer to new server...

    What does the sp_help_revlogin do?

    The sp_help_revlogin SP is a Microsoft provided utility that generates a TSQL script to migrate logins from one server to another. This SP will not only copy the existing logins, but it will also copy the passwords and Security Identification Numbers (SID) associated with SQL Server Authenticated users.


    Raju Rasagounder Sr MSSQL DBA


    • Edited by RAJU RG Sunday, June 29, 2014 11:38 PM
    • Proposed as answer by Ashwin Menon Monday, June 30, 2014 9:29 AM
    • Marked as answer by User172 Monday, June 30, 2014 1:20 PM
    Sunday, June 29, 2014 11:34 PM
  • Thank you in advance for your help with this question.

    We have to move SQL 2000 and existing db to a replacement server.  I am following http://support.microsoft.com/kb/246133 to transfer logins.  I need to confirm our ERP application can access the SQL 2000 db on the replacement server before shutting down the existing server.  Will running the sp_help_revlogin stored procedure on the existing server to extract logins delete the logins on the existing server after exporting them?

    I need to confirm the ERP application will run on the replacement server, so I do not want to delete the logins on the existing server.  I need the existing server to continue functioning until I can confirm the replacement server is working.

    Thank you again.


    User172

    Hi,

    If I understand your question correctly you are just moving SQL Server instance so you need to follw this method

    To transfer logins and passwords from a SQL Server 7.0 server to an instance of SQL Server 2000, or between two instances of SQL Server 2000, you can use the new DTS Package Transfer Logins Task in SQL Server 2000. To do this, follow these steps:

    1. Connect to the SQL Server 2000 destination server, move to the Data Transformation Services in SQL Server Enterprise Manager, expand the folder, right-click Local Packages, and then click New Package.
    2. After the DTS package designer opens, click Transfer Logins Task on the Task menu. Complete the information about the Source, Destination and Logins tabs as appropriate.

      Important The SQL Server 2000 destination server cannot be running the 64-bit version of SQL Server 2000. DTS components for the 64-bit version of SQL Server 2000 are not available. If you are importing logins from an instance of SQL Server that is on a separate computer, your instance of SQL Server will must be running under a Domain Account to complete the task.

      Note The DTS method will transfer the passwords but not the original SID. If a login is not created by using the original SID and user databases are also transferred to a new server, the database users will be orphaned from the login. To transfer the original SID and bypass the orphaned users, follow the steps in the "A complete resolution to transfer logins and passwords between different versions of SQL Server" section.

    SQL server 2000 is outdated and I have not worked on ERP with SQL 2000 but above suggesstion will work

    On new server for each database please run below to check for orphaned user

    EXEC sp_change_users_login 'Report'

    Have alook at below link to fix the issue if report gives orphaned user

    http://technet.microsoft.com/en-us/library/aa259633(v=sql.80).aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles



    Monday, June 30, 2014 9:25 AM
  • Dear Raju Rasagounder, thank you very much for your help.  I really appreciate it.

    User172

    Monday, June 30, 2014 1:21 PM