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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Hi, I have migrated a database from sql...

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...

Rate Topic

Display Mode

Topic Options

Author

Message

nikhil.theegela

Posted Tuesday, August 17, 2010 8:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 14, 2011 9:38 PM
Points: 5, Visits: 34

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

Post #970849

Steve Jones - SSC Editor

Posted Tuesday, August 17, 2010 9:45 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #970865

nikhil.theegela

Posted Tuesday, August 17, 2010 10:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 14, 2011 9:38 PM
Points: 5, Visits: 34

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.

Post #970893

Adiga

Posted Tuesday, August 17, 2010 11:06 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:23 PM
Points: 1,618, Visits: 20,901

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

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Post #970903

punam_kumari

Posted Wednesday, August 18, 2010 9:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 18, 2010 8:48 AM
Points: 1, Visits: 2

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.

Post #971208

Perry Whittle

Posted Wednesday, August 18, 2010 9:18 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165

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" Wink