Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Unanswered: Migrating SQL Server 2000 Logins

    I'm changing servers and want to copy all the logins from the old server to the new server. Is there anyway to do this?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, it can be done. Microsoft supports scripting logins via SQL-DMO (which is used by SQL Enterprise Manager). It will script your logins, but it misses details that are sometimes important to me (like SID values and passwords). They script this way for good reason, since that avoids all kinds of security issues.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    you can script them
    right click the database in enterprise manager
    ALL Tasks Generate SQL Script
    select the show all button and click the options tab
    select the "Script SQL Server Logins(Windows and SQL Server Logins)" check box
    OR
    create a dts package and use the transfer logins task.

    either way get familiar with the sp_helplogins stored procedure

  4. #4
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    execute the below script inside your datbase and capture the output,
    this output you need to execute on your secondary location db

    SELECT 'execute sp_addlogin '''+ left(name + ''', ',100), password, + ', ''' + db_name(dbid) + + ''', ''' + + language + ''', ', sid, ', skip_encryption' FROM master.dbo.sysxlogins where srvid is null and xstatus&4 <> 4 order by name

    this output will create the login/user with same password and settings

    Hope you will get that you want...

    Cheers!!
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

  5. #5
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    29
    If you are changing servers, isnt it enough to backup the Application database in addtion to master,msdb and model -- and just restore these on the new machine.

    Cheers!

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    http://support.microsoft.com/default.aspx?kbid=246133 to transfer logins with passwords.

    http://support.microsoft.com/default...b;en-us;240872 to resolve the login permission once you move them.

    Also follow corresponding KBAs referred.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •