Wednesday, January 18, 2006

Migrating Logins from One SQL Server to Another

UPDATED! 11/11/11
Every DBA runs into the following topic from time to time in their career, How Do you go about Migrating Logins from One SQL Server to Another? From time to time you'll want to move actual sql login users to another server be it an upgrade, or if you have a good development - test - production cycle, you might be more used to this. Or maybe just maybe you're starting this cycle out, initially just run the Sproc provided by Microsoft called sp_help_revlogin There are two methods posted on the Microsoft site:

  • Method 1 is for Sql Server 2000 to 2000  KB 246133
  • Method 2 is for Sql Server 2000 to 2005  KB 246133
  • Method 3 is for Sql Server 2005/2008 KB 918992

    1. You will need to use Query Analyzer for Sql Server 2000 or Management Studio for 2005/2008 
    Copy the results and paste them to the new query window
    1. Go to the source server and open up a new query window and be sure you are pointing to your source database
    2. check that your Management studio is set to return your results as text
    3. you can do this by right clicking within the query window then choosing results to text
    4. Paste your script into the query window and execute the script by clicking the run button or F5.
    5. copy the results and open up a new Management Studio Query window that is connected to your Destination Server and be sure you've selected the destination database.
    6. Paste the result text, and edit out any users you don't want to re-create
    Now that you've migrated the users you can simply backup your database as you normally do and restore it to the new environment.

    These steps are only necessary if you need to migrate users from one physical server to another. You won't need to run these steps if you are on the same server and you've simply created your test database in the same environment, the users already exist in the database from your backup and restore, and they already exist in the server system database as well. This is only necessary when you have two completely separate servers/instances.

    Good luck and happy TSQLing

    No comments:

    Post a Comment