locked
SQL 2005 - invalid object name error RRS feed

  • Question

  • Hi there,

    I got a problem when I’m doing select statement like select * from kuaf, Invalid object name kuaf will be prompt but it will ok if I use select * from llink.kuaf and the result is positive. This happen after I did reattach the database on SQL 2005. I did reattach .mdf file successfully. I created new user(llink) and then tried to change all the owner of the database, table, view and all object owner to this user(llink). I login to the database using llink user and doing select statement. So weird when the error prompt and need to put llink.table. Why I need to put the object owner there? Thank you.
    Friday, June 27, 2008 8:20 AM

Answers

  • It sounds as though the default schema for the new user is not set to the schema that the object is nested in.  Schemas are full implemented in sql 2005 while in 2000 they existed, somewhat it simply showed the owner depending on your database most schemas are probably owned by dbo.

     

    In sql 2005 the full implementation of schemas means that when a user SELECTs from a table if the users default schema is not set to that schema and the schema of the object is not dbo then the object can not be bound and you will receive the message you have noted.  When executing a query sql first looks for the object within the users default schema and if the object is not found sql goes to dbo schema and looks there.  If the object is in neither schema then the query fails.  You can resolve this by changing the schema of the object to dbo so that regardless of who is querying they will not need to provide the qualified schema and object name.

     

    Hope this helps.

     

     

    Friday, June 27, 2008 4:22 PM