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


      • 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.
    6. 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.
    7. 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

    Comments

    Post a Comment

    Popular posts from this blog

    Service Broker and External Activator for Sql Server...

    -
    I ntroduced in Sql Server 2005, Microsoft's Service Broker is a Messaging Queue for Sql Server.  You can audit tables, trigger events, and even call web services (Using the External Activator). Follow this excellent tutorial by Dev Kimchi which helps you step by step and clearly explain how to setup your Service Broker messaging queue. To get started with the external activator you will require the version for the version of Sql Server you are running, click on any of the Microsoft download links and follow the install instructions: Microsoft® SQL Server® 2012 Feature Pack Microsoft® SQL Server® 2014 Feature Pack One last thing before you get frustrated installing the External Activator service on a remote server.  At least for the 2012 version, it still required .Net 3.5 installed on the destination computer/server.  If you are trying to install on a Windows 2012 server follow this helpful guide on how to get .Net 3.5 installed now that the latest packs have b...
    Read more

    List to Table

    -
    E very now and again, a developer will be faced with the task of referincing a List of id's. It's typically irresistable to want to push all these IDs into a single field. If you're searching then you may end up considering a cursor... for that I decided to write the following function... it's like other Tsql functions that push a list to a table, but this is my take on it... I release the code here with out warranty. CREATE FUNCTION udfListToTable (@HList VarChar(2000), @Delimiter CHAR(1)) RETURNS @ListTable TABLE (Field1 VARCHAR(6)) AS BEGIN --By: Francisco Tapia --Date: 2/1/2005 --Purpose: To convert a Comma delimited text to a Temp Variable table To help avoid dynamic sql -- Instead you can join the temp table or use it in your where clause if a field is IN the subquery DECLARE @FieldText as VarChar(6) IF RIGHT(RTRIM(@HLIST),1) @Delimiter SET @HList = @HList + @Delimiter WHILE CHARINDEX(@Delimiter, @HList) > 0 BEGIN ...
    Read more

    Turbo Charge Your SSMS

    -
    The other day a co-worker mentioned one of his biggest pet peeves about Sql's new IDE. Of course he likes the old IDE, but what he detested the most was the slow bloat time it took to get his SSMS loaded on the screen. When we timed the boot time for his Enterprise Manager to load it took nearly 7 seconds. Not bad, but then proceeded to tell me how much longer SSMS (Sql Server Management Studio) took to load. I guessed that it could possibly be about 10 seconds (only 3 more seconds) but I was shocked to see it load in nearly 25 seconds. it was nearly 3x less productive just loading the thing before you could connect to do anything. I couldn't believe that it was nearly that slow on his computer, I had never timed it on my pc. So when I returned I had to try it. Again the load time was nearly 20 seconds on my machine (about 18 seconds to be exact). I was not entirely pleased with my results but thankfully Google provided the answer, one quick search for "Turbo char...
    Read more