Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hi, I have migrated a database from sql server 2000 instance to another sql server 2000 instance.So...


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

Author
Message
nikhil.theegela
nikhil.theegela
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 43
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36070 Visits: 18736
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
My Blog: www.voiceofthedba.com
nikhil.theegela
nikhil.theegela
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 43
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.
Adiga
Adiga
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1634 Visits: 21012
Here is how to use it. It will help to figure out orphaned users and fix it.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
punam_kumari
punam_kumari
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8780 Visits: 16554
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" ;-)
nikhil.theegela
nikhil.theegela
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 43
hi punam,
Thanks for the reply.I got the syntax now.
nikhil.theegela
nikhil.theegela
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 43
Thanks for the reply.i got the syntax now.
nikhil.theegela
nikhil.theegela
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 43
hi perry,
Thanks for the reply.I got the solution now.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search