Post reply

Migrating Logins To Another Server

  • September 28, 2003 at 12:00 am

    #159895

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/glarsen/migratingloginstoanotherserver.asp

    Gregory A. Larsen, MVP

  • September 30, 2003 at 3:38 am

    #476328

    I have experienced problems transferring logins so this article will be very useful if I have to do it in a live situation. Just one clarification.

    Would it be necessary to run this script if I restored backups to a completely new server. I imagine the order would be to transfer the system databases first followed by the user databases. Would the logins and users be correctly linked after the restore?

  • September 30, 2003 at 6:03 am

    #476347

    I just use the script to transfer the logins from a DEC ALPHA SQL 7 server to a new Intel SQL 2000 server with no problems. I was unable to use the Transfer Logins DTS job on this combination. Timely ... Absolutely :>)

  • September 30, 2003 at 7:17 am

    #476358

    It does not transfer the default database though.

  • September 30, 2003 at 8:51 am

    #476383

    When you transfer master all the logins will come across so you would not have to run this script. This script is used to copy logins and passwords without restore the master database.

    Gregory A. Larsen, DBA

    Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here:http:www.sqlservercentral.com/bestof/purchase.asp

    Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • September 30, 2003 at 9:00 am

    #476385

    I agree it would be nice to transfer the default database. As the script comes from Microsoft it does not set the default database, and that is what I presented in the article.

    Normally if I am only moving one database I modify the sp_help_revlogin SP to only select logins if they are associated with the database I am moving and I add "execute master..sp_defaultdb " commands to the SP to make sure the default db get set.

    It could also be modified to determine the default database and set it appropriately. This would be a good enhancement to this script.

    Gregory A. Larsen, DBA

    Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here:http:www.sqlservercentral.com/bestof/purchase.asp

    Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • September 30, 2003 at 9:39 am

    #476396

    Can anyone remember the SP that fixes orphaned users?

    I seem to remember that if the login name and user name were the same it assigns a matching SID.

    LinkedIn Profile

  • September 30, 2003 at 10:26 am

    #476406

    Is sp_change_users_login you are looking for?

  • October 1, 2003 at 1:49 am

    #476493

    Thats the baby. Thanks

    LinkedIn Profile

  • September 30, 2004 at 6:04 am

    #524896

    This would be a lot better if it was fit to screen size rather than having to scroll back and forth to read it...

  • October 5, 2004 at 7:07 am

    #525382

    When using the script to transfer logins and passwords from one server to another. After doing so... Does the logins and passwords still appear on the server transfering from??? I am doing a parallel install and need to keep the production database logins and passwords still intact.

     

    Thanks

    Chris

  • September 30, 2009 at 9:22 am

    #1060020

    Hi,

    Does using the script from sp_help_revlogin also copy the user's permisions?

    Thanks,

    Joel

  • March 11, 2011 at 1:44 pm

    #1297865

    is this still possible with sql server 2005/2008 with the new DMV's?

Viewing 13 posts - 1 through 12 (of 12 total)

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