none
login script to move an instance of sql 2008 R2 to another

    Question

  • Hi,

    I need to know what are the steps to capture logins when we move an instance from one server to another. I know the sp_helplogins but the issue is the login can be created in the destination server, but both the windows and sql logins doesn't have rights. Is there any way to remap that.

    In sql 2005 sp_change_users_login used to remap, but this doesn't work in sql 2008 R2

    Thanks

    Friday, May 18, 2012 8:48 AM

Answers

All replies

  • please see this link

    http://support.microsoft.com/kb/918992

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Friday, May 18, 2012 8:54 AM
  • I already visited the above link but I don't know how the logins are mapped in sql 2008 R2. I can't do it manually because the no. of users are pretty high. Can you please let me know the better way.

    Thanks

    Friday, May 18, 2012 10:27 AM
  • In sql 2005 sp_change_users_login used to remap, but this doesn't work in sql 2008 R2

    hello Nibras

    could you please explain why sp_change_users_login does not work with SQL 2008R2 ?

    if you backup & restore a database from one server to another (eg. 2005 to 2008R2) the database may contains database users which do not exists a SQL Login.

    Running sp_change_users_login with the option 'Report' generates a list of users which are not mapped or which do have different SID.

    Running it again for each users does fix the SID difference and creates a SQL login if it does not yet exists.

    • Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.

    and it definitively still works in SQL Server 2008R2.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, May 18, 2012 10:47 AM
  • Friday, May 18, 2012 11:00 AM
  • Hi Daniel,

    I tried creating the sp_change_users_login script but it throws an error as

    Ad hoc updates to system catalogs are not allowed.'

    1. Post exeuction of sp_help_revlogins, the logins are created but with no permissions and roles assigned to the database in the destination server. What is the way to bring everything like logins, roles and the settings intact.

    Friday, May 18, 2012 11:06 AM
  • HI Steve,

    Thanks for the response.

    The simple process doesn't really transfer the roles associated to  a login, but in the second one should I need to create a SSIS to accomplish that. Is there a simple way like an script to capture the mappings.

    Friday, May 18, 2012 11:21 AM
  • if you don't use SSIS then you need to use the linked server and use the similar code in what this SSIS package use.

    But what's worng with SSIS package? I believe the SSIS has many advantage for transfering the logins, and it's role membership.

    Friday, May 18, 2012 1:05 PM
  • Hi Steven,

    I agree with you but I am not good in SSIS. Is there an easy way out.

    Friday, May 18, 2012 2:50 PM
  • Hi Nibra,

    I used to for login script sp_helprevlogin script.

    can you pls try that, its worked for me :)

    thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Friday, May 18, 2012 3:22 PM
  • Hi Sathish,

    sp_helprevlogin did worked for me in recreating the login but the access permission is not there when you exeucte in destination server.

    Tuesday, May 22, 2012 11:50 AM
  • Hello,

    It might be too late to answer but it might help to others in future.
    There are 5 steps to migrate all logins with passwords for sql logins successfully.
     
    1. Run the script which is located on http://support.microsoft.com/kb/918992 .This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin.

    2. run EXEC sp_help_revlogin

    The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.
     
    3.Open a new Query Editor window on destination server, and then run the output script that is generated in step 2
     
    4. Restore your databases after migrating logins which will avoid to have many orphans user even I still suggest you to check orphans users by running SP mentioned in step 5.
     

    5. sp_change_users_login 'report'

    thanks,

    Thanks, Chhavinath Mishra. Please mark as this post as answered if my answer helps you to resolves your issue :)

    Thursday, September 06, 2012 7:51 AM
  • Passwords do not migrate on my SS2008 R2 instances. 
    Tuesday, April 30, 2013 9:04 PM