July 9, 2006 at 5:44 am
We took a backup of the mdf and ldf datafiles from one SQL Server and attached to another. The owner of the database in the source and target database is a SQL User dope24_sa. All the tables in this database are owner by this user except for the system tables which are owned by dbo.
When we run a query on this attached database, I get the error as follows:
select count(*) from airline
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'airline'.
However, when I qualify the owner name before the object name, the query runs successfully.
select count(*) from dope24_sa.airline
921
After I changed the ownership of the table from dope24_sa to dbo, I am able to run the first query successfully.
I also ran the sp_change_users_login stored procedure, but the user is already associated with the database:
EXEC sp_change_users_login 'Update_One', dope24_sa ', 'dope24_sa '
The number of orphaned users fixed by updating users was 0.
In the source database the owner of the objects is dope24_sa and we are able to query the objects without qualifying the owner name. I have checked the permissions in the target database is similar to the one in source.
Appreciate if you can help us on this.
Regards.
Shahid.
July 9, 2006 at 2:34 pm
I am guessing that dope24_sa was an attempt to create a sysadmin role that wasn't the SA login.
Prior to SQL2005 NOT having objects owned by the dbo was always ended up being a problem.
When you run a query on an object not qualified by the owner it will first look for the object owned by the current user and when it doesn't find that object it would fall back to the dbo user.
This means that SQL2000 would have to do two checks instead of one. That is why the sql performance website always recommends qualifying objects with their owners.
July 10, 2006 at 12:50 am
just to add to David's reply :
When you are logged in as member of the sysadmin fixed server role, internaly the user is switched to "sa" which defaults to schema "dbo".
The step to find objects with schema = username is not done !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 11, 2006 at 12:27 am
Thanks so much guys.
The problem was what alzdba suggested. The user was granted System Admin role and hence needed to be qualifyed. After I remove the role privilege, all seem to be fine.
Regards.
Shahid.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply