locked
How to transfer logins and users with passwords from SQL Server 2008 R2 to SQL Server 2014 ? RRS feed

  • Question

  • Hello Friends,

    I want to migrate one of my application from SQL Server 2008 R2 Enterprise Edition to SQL Server 2014 Enterprise Edition.

    Hence, looking forward for suggestions - for transferring all the Logins & Users with Password from SQL Server 2008 R2 to SQL Server 2014.

    Could someone please come up with some suggestions?

    Thanks,

    Vinay


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia


    • Edited by vinaypugalia Tuesday, August 25, 2015 8:01 AM typo
    Tuesday, August 25, 2015 8:00 AM

Answers

All replies

  • Hello Vinay,

    For this MS provides a T-SQL script, see How to transfer logins and passwords between instances of SQL Server


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 25, 2015 8:22 AM
  • Hi Olaf,

    Yes, I came across this link earlier but did not proceed with this as it mentions - 

    "This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005, of Microsoft SQL Server 2008, and of Microsoft SQL Server 2012 on different servers."

    And in my case I have got SQL Server 2014 involved.

    Will it still work??

    Thanks,

    Vinay


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Tuesday, August 25, 2015 11:57 AM
  • Yes, it will work with SQL Server 2014, too.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 25, 2015 12:02 PM
  • I don't see why not as long as the collation is the same for the two SQL Servers.

    Also having used this on a number of occasions there are a couple of things worth mentioning.

    Remove the sa login and any other login that you don't want to transfer.

    Check that the default database specified for the logins exists on the new Server; if not change this setting to another existing database.

    to map SQL Authenticated Database logins to the Logins you are about to create you will need to run sp_change_users_login for each login in every database.

    https://msdn.microsoft.com/en-us/library/ms174378.aspx


    Please click "Mark As Answer" if my post helped. Tony C.


    • Edited by Anthony C-UK Tuesday, August 25, 2015 12:12 PM
    • Marked as answer by vinaypugalia Tuesday, August 25, 2015 1:48 PM
    Tuesday, August 25, 2015 12:09 PM