restoring a 2000 db to 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

restoring a 2000 db to 2005

Author

Topic

Mickoni
Starting Member

8 Posts

Posted - 2007-06-20 : 10:31:02

Hello,

I'm having an issue with stored procedures after restoring a db from 2000 -> 2005.

If any stored procedures isnt owned by dbo (eg tomsqluser.updateItems rather than dbo.updateItems) then it just wont work and Studio Management Server doesnt give the permission to change it to dbo.

Is there any way around this after restoring?

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 10:48:01

Presumably under SQL 2000 you / your application was logging on to the Server as "tomsqluser" in order that the Sproc's owned by "tomsqluser" were used.

So under SQL 2005 if you log in as "tomsqluser" the same should be true.

You probably need to have SysAdmin rights in order to change database owner - if that's a problem you could perhaps do this under SQL 2000, and then Backup & Restore again.

Did you fix the orphan users after restoring on SQL 2005? (Otherwise the "tomsqluser" in your Database will NOT be synchronised with any pre-existing "tomsqluser" in your 2005 Server.

Kristen

Mickoni
Starting Member

8 Posts

Posted - 2007-06-20 : 10:59:56

Hi Kristen,

Many thanks for the reply.

Yes, "Tomsqluser" connects from web app and orignally created the sproc.

I have SA access and tomsqluser is the Datbase owner but it still doesnt like it.

I think you are right thouhgh, the issue is that the oprhaned user has same name as the new user (pre-created tomsqluser). How do you synchronize the users though?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-20 : 11:15:44

Is there sql login of tomsqluser on target server?

Mickoni
Starting Member

8 Posts

Posted - 2007-06-20 : 11:17:35

Hi Rmiao,

yes, there is. That was created before the restore.

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 11:35:38

"That was created before the restore"

The Restore will not have re-synchronised "tomsqluser" in the database with "tomsqluser" on the Server (yeah, its a bit of an arse, but Server Logins have an ID, and that is only unique to that server ...)

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Fix%20Orphaned%20Users

Did you change the Compatibility to 90, so you can take advantage of new feature sin SQL 2005? The Restore won't do that either!

Kristen

Mickoni
Starting Member

8 Posts

Posted - 2007-06-20 : 12:01:35

Kristen,

I'm all a bit new to this, so just getting my head around it. I apologise. Now in theory, if I just want to remap tomsqluser (from DB) to tomsqluser (on SQL server) I can just run this command?

sp_change_users_login 'update_one', tomsqluser, tomsqluser

However, I get this message:
Msg 15063, Level 16, State 1, Procedure sp_change_users_login, Line 143
The login already has an account under a different user name.

I have also tried:

sp_change_users_login 'auto_fix', '4079sql'

The row for user 'tomsqluser' will be fixed by updating its login link to a login already in existence.
Msg 15331, Level 11, State 1, Procedure sp_change_users_login, Line 257
The user 'tomsqluser' cannot take the action auto_fix due to duplicate SID.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.

Any pointers?

Kristen
Test

22859 Posts

Posted - 2007-06-20 : 12:40:44

"can I just run this command?"

I don't think it is significant (SQL Server will probably accept it as you've typed it) but the last two parameters should be quoted:

USE MyDatabase
GO
EXEC sp_change_users_login 'update_one', 'tomsqluser', 'tomsqluser'

if that fails, and there is nothing important about your "tomsqluser" login (i.e. NO other databases referencing it, no scheduled jobs set up to login using that UserID, etc.) you could drop it, and make a new one, and THEN associate it with the one in your restored database:

EXEC sp_droplogin 'tomsqluser'
GO
EXEC sp_addlogin 'tomsqluser', 'MyPassword', 'MyDatabase'
GO
EXEC sp_change_users_login 'update_one', 'tomsqluser', 'tomsqluser'

This may all go a bit pear-shaped if you are logged in as "tomsqluser" at the time!! so best to login as SysAdmin before doing this.

Kristen

Mickoni
Starting Member

8 Posts

Posted - 2007-06-29 : 04:30:20

Just an update. No luck with the above Kristen.

Due to user hassle, I fudged together a script to change all the stored procedures to eg tomsqluser.StoredProcedure to dbo.StoredProcedure. That fixes the issue. However, same problem on next restore only none of the table can be accessed. I dont want to do this for every restored DB!

I was thinking maybe the issue is the user mapping in Security->Logins. The user is set at as dbo with the default schema of dbo. However, when I try to change the default schema to eg tomsqluser i get the following message

"Cannot alter the user 'dbo'"

Am I barking up the wrong tree here??? :-)

Kristen
Test

22859 Posts

Posted - 2007-06-29 : 04:46:21

Definitely odd, but I can't think what is causing the problem.

Hopefully someone will along in a minute.

Kristen

Gawaine79
Starting Member

6 Posts

Posted - 2007-06-29 : 09:40:53

I would recommend dropping the tomsqluser from the new sql2005 instance.

Then on the old server 2000 server follow the steps listed in this article (http://support.microsoft.com/kb/246133) and create the procedures sp_hexadecimal and sp_help_revlogin on master.

Then run sp_help_revlogin and copy the script portion for user tomSQLuser and run it on the new sql2005 instance.
Now when you log in as tomSQLuser on the 2005 instance it should have access to everything it did on the old 2000 instance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources