Post reply

Hi, I have migrated a database from sql server 2000 instance to another sql server 2000 instance.So i have taken the backup of the Tenu001 database and i have restored it onto the target instance.Then later on i tried to transfer the logins and passwords from the source instance to the target insta

  • August 17, 2010 at 8:45 pm

    #224885

    Hi,

    I have migrated a database from sql server 2000 instance to another sql server 2000 instance.So i have taken the backup of the Tenu001 database and i have restored it onto the target instance.Then later on i tried to transfer the logins and passwords from the source instance to the target instance by executing the scripts from the following blogs/:http://support.microsoft.com/kb/918992/en-us, http://support.microsoft.com/kb/246133, http://blogs.techrepublic.com.com/howdoi/?p=140.So it showed me the message that the logins got transfered.Then later on i transferred the jobs of (Tenu001 Database) to the target instance by generating the scripts.So my problem is when i go to the target instance and check for the users it shows me that their logins have not yet been transferred.So they are orphaned users still existing.I have tried to run [sp_change_users_login]can fix issue but cannot resolve this problem.I had been trying hardly but cannot fix this issue.Please could any body give me a step by step procedure and code in detail.I am new to sql server.Please couls anybody give me the best solution to get rid of this problem.

    Thank You

  • August 17, 2010 at 9:45 pm

    #1208260

    You've listed a lot here, but it's not clear exactly what you did.

    Did you run sp_help_revlogin (the 2000) version on the SQL Server 2000 server?

    Did you then run the resulting script on the 2008 server? I assume all your logins got created, is that correct?

    Then with the database restored, did you try to fix orphaned users? What did you run? This is a fairly simple process. You resync the logins with the users with sp_change_users_login. It's not more complicate than that, assuming everything ran without errors.

    Please format your posts so it isn't one continuous stream of thoughts. That is very hard to read.

  • August 17, 2010 at 10:47 pm

    #1208285

    hi Steve,

    thank you for the reply.Yes i did use the sp_revlogin executed it and ran the resulting output script on the target instance which is sql server 2000.So the logins got transfered.But i am unable to resyncronise the logins with this sp_change_users_logins.May be i dont know the exact script because i am new to SQL Server.So please could you let me know how to use sp_change_users_logins script.Because i tried to use it but not able to resyncronise the logins.please could you send me the complete syntax.

    and(this is migration process between sql server 2000 to sql server 2000.

    Thank you.

  • August 17, 2010 at 11:06 pm

    #1208292

    Here is how to use it. It will help to figure out orphaned users and fix it.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • August 18, 2010 at 9:04 am

    #1208553

    Please find the relevant steps in users and login mapping process:

    1) When we restore a database, the users associated with the database gets restored.

    2) using sp_help_revlogin, we generate the script for Logins and run this script on the new instance.

    3) Now we have the users and logins both on the new instance but they are not mapped.

    4) To check for orphan users, run this script:

    sp_change_users_login @action='report'

    This needs to be run within the desired database context.For example if want to check orphan users for Pubs database then run within it.

    5) Now to map these orphan users with their respective logins, we need to run this command:

    USE PUBS

    GO

    sp_change_users_login @Action='update_one',

    @UserNamePattern='TestUser',

    @LoginName='Testlogin'

    GO

    6) The above steps will fix the issue. Run the script for checking the orphan users again and validate that no more users are orphan.

  • August 18, 2010 at 9:18 am

    #1208563

    Firstly, it would help if you post in the correct forum location for help with your issue, that aside the link at

    http://support.microsoft.com/kb/918992/en-us

    does not apply to your scenario in any way and could cause confusion.

    You need to follow this link

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

    and use method 1 to transfer the logins

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • August 18, 2010 at 7:43 pm

    #1208851

    hi punam,

    Thanks for the reply.I got the syntax now.

  • August 18, 2010 at 7:44 pm

    #1208852

    Thanks for the reply.i got the syntax now.

  • August 18, 2010 at 7:45 pm

    #1208853

    hi perry,

    Thanks for the reply.I got the solution now.

Viewing 9 posts - 1 through 8 (of 8 total)

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