Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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... Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 8:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 17, 2010 9:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:30 PM
Points: 33,055, Visits: 15,167
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
Posted Tuesday, August 17, 2010 10:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 17, 2010 11:06 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 1,618, Visits: 20,978
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
Posted Wednesday, August 18, 2010 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, August 18, 2010 9:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 6,163, Visits: 13,306
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"
Post #971219
Posted Wednesday, August 18, 2010 7:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 14, 2011 9:38 PM
Points: 5, Visits: 34
hi punam,
Thanks for the reply.I got the syntax now.
Post #971562
Posted Wednesday, August 18, 2010 7:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 14, 2011 9:38 PM
Points: 5, Visits: 34
Thanks for the reply.i got the syntax now.
Post #971563
Posted Wednesday, August 18, 2010 7:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 14, 2011 9:38 PM
Points: 5, Visits: 34
hi perry,
Thanks for the reply.I got the solution now.
Post #971564
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse