dot Notation in query for attached DB
-
Hi all,
I have just moved a database created on SQLServer 2000 from one server to to another by attaching then detaching. it was detached from SQLServer on win 2000 to sql server in win xp. Both are non-server machines.
My problem is that a simple query analyser select query like "select * from tb1"
must now be rewritten "select * from db.tb1" to execute otherwise I am given a message like
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'tb_country'.
I suspect this problem is because of my security setting but I ma not sure. i have tried all I know with no success...
PS: I am using sql server authentication for this project..
Can anyone help ?
Thanks in advance
-
The issue is because these objects are owned by a different user, not the user who is trying to execute the query.
It may also be the case of orphan users which is a very common issue with Detaching the databases from one server and Attaching them to another server. If the Login that owns the objects does not exist on the new server, the Database User become orphaned.
You need to create this Login and then run sp_change_users_login stored proc to fix the issue.
EXEC sp_change_users_login 'Update_One', <db user name>, <login name>
Hope this helps
M.S. Reddy
-
Many Thanks Reddy,
That is exactly what I needed - It was an orphaned Login and I can now run simple queries (without the table ref.)
EXEC sp_change_users_login 'Update_One', <db user name>, <login name> did the trick.
Now i can move onto other things......
Cheers,
Tuka
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply