[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server 2000 to 2008 - Upgraded databases, and now have a DB User issue

Posted on 2011-10-23
9
Medium Priority
?
1,731 Views
Last Modified: 2012-05-12
I have successfully moved a database from 2000 to 2008R2.  Everything works, HOWEVER, there was ONE SQL user in the 2000 server that I did not  move.  The user still exists in the DB on the 2008R2 server, presumably now orphaned.

What I need to do is move the user from 2000 to 2008, including the DB password.  Then I think what I have to do is delete the orphaned user in the 2008R2 database that moved, and re-add the moved database user and assign appropriate permissions.  Am I wrong here?  

I've tried running the Microsoft script for moving SQL users from SQL 2000 to 2005, and have been unsucessful (http://support.microsoft.com/kb/246133/).  After I paste the attached text from OPTION 2 to a file with an .SQL extension, the script runs and errors out with:  

Msg 208, Level 16, State 1, Procedure sp_help_revlogin_2000_to_2005, Line 19
Invalid object name 'master.dbo.sysxlogins'.

I have tried running it on the SQL 2000 server, on a SQL 2005 server connecting to the SQL 2000 server from the management console, and from the SQL 2008 server, and I keep getting the error listed above.  

I do not need to move ALL users, I only need to move ONE particular user.

Can anyone help with this?  
0
Comment
Question by:jkeegan123
  • 5
  • 4
9 Comments
 
LVL 5

Expert Comment

by:Brian Chan
ID: 37015821
It is my guessing, did you look at the compatability level?
0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 37015855
Apparently, there is no sysxlogins table since SQL 2005. The problem is that it one of those undocumented item In SQL 2000. People suggested to use sys.server_principals & sys.sql_logins instead. Yeap you need to change the code.

0
 
LVL 5

Author Comment

by:jkeegan123
ID: 37015885
Given the example script in http://support.microsoft.com/kb/246133/, how would I have to make this change?  I tried changing "sysxlogins" to "sys.sql_logins" and/or "sys.server_principals" and it did not fix the issue...it threw back an error:

The request for procedure 'sql_logins' failed because 'sql_logins' is a view object.

Any suggestions?
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 5

Expert Comment

by:Brian Chan
ID: 37015912
Ok, let's clarify it. So you are using that script listed in the "method 2" section on your SQL 2000 box, right? and then, as instructed, paste and run the output in SQL Server Management Studio on the destination SQL Server 2005. after that you have the error returned, isn't it?
0
 
LVL 5

Author Comment

by:jkeegan123
ID: 37015915
I get the mentioned error when I paste the code into the SQL 2000 server, I do not get any output other than errors.  If I'm supposed to change the code that I paste in any way, I don't realize it and I am not doing so.  Is there perhaps an easier way to move one single user from one server to another?
0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 37015963
try this on your SQL 2000 box:

SELECT *
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'

Does it return anything?
0
 
LVL 5

Accepted Solution

by:
jkeegan123 earned 0 total points
ID: 37016074
results from that query:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.dbo.sysxlogins'.

I did successfully just run the script from the following page which finally returned the values that I needed to create the userid and passwords.  Very neat:

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

From here I was able to run the resulting query and get the missing user(s) created.  NOW I have my user in my database AND a matching user in my SQL server instance...when I did a stored procedure to check orphaned users, it didn't report any, so I assume it matched up (since the SIDs were the same).  
0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 37016594
I believe that it is some related to naming of those the object and and version difference between earlier version and the SQL 2005 upward. When I am trying to research for your issue, it is just standing out as like everyone has experienced the very similar issue regard to this very specific system object.

On the other hand, the later article you posted, it applies to SQL 2005 and SQL 2008 only. Well as long as you got it work at you end, good to hear that. And thanks to share the link for login transfer. learn something new today :)
0
 
LVL 5

Author Closing Comment

by:jkeegan123
ID: 37199895
This was the answer.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question