locked
Invalid object name error on owned object RRS feed

  • Question

  • A user wanted the ability to create, backup, and restore DBs on a SQL 2000 SP4 server. I added that user to the sysadmin and now he is experiencing an error where when select * from an owned object, it reports "Invalid object name <objectname>'. When separating it with the dot notation, it works.

    The user did not experience this prior to being added in the sysadmin role. Any ideas why?

    Thanks.

    Wednesday, September 14, 2011 5:08 PM

Answers

  • Hi rhchin,

    The sysadmin has a default schema, if you haven’t assign a default schema, the sysadmin’s default schema is dbo. So when the user who is sysadmin selects from an owned object, if the object is in the sysadmin’ s default schema, the user could use select * from an owned object, then the user could get the answer he wants; if the object is not in the default schema, then the user will get the error” Invalid object name <objectname>”. So best practice is use select * from schema.objectname.

     


    Hope this helps.

    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Maggie Luo Friday, September 16, 2011 8:35 AM
    • Marked as answer by rhchin Wednesday, September 21, 2011 2:27 AM
    Friday, September 16, 2011 8:34 AM

All replies

  • Is that Table Created Under default User Dbo Or Some Other User ??

    If A SysAdmin Will Access Table Like Select * From TableName then SQL Will Treat It As Select * From dbo.TableName

    To Access Other Users Table You Will have to Provide User.TableName : select * from User.TableName

     


    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Wednesday, September 14, 2011 5:43 PM
  • :-) As always...always...always name the schema you are trying to access the objects from. That is much more determinisitic than just naming the ojectname itself. So always use SELECT Something FROM dbo.SomeTable instead of SELECT Something FROM SomeTable.

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    • Proposed as answer by TiborKMVP Wednesday, September 14, 2011 8:09 PM
    Wednesday, September 14, 2011 6:41 PM
  • Agreed. Being sysadmin, you have a different rules for default schema, so the answer is to either not be sysadmin (which can indeed be a thing byitself to consider!) or specify the schema.
    Tibor Karaszi, SQL Server MVP | web | blog
    Wednesday, September 14, 2011 8:11 PM
  • Hi rhchin,

    The sysadmin has a default schema, if you haven’t assign a default schema, the sysadmin’s default schema is dbo. So when the user who is sysadmin selects from an owned object, if the object is in the sysadmin’ s default schema, the user could use select * from an owned object, then the user could get the answer he wants; if the object is not in the default schema, then the user will get the error” Invalid object name <objectname>”. So best practice is use select * from schema.objectname.

     


    Hope this helps.

    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • Proposed as answer by Maggie Luo Friday, September 16, 2011 8:35 AM
    • Marked as answer by rhchin Wednesday, September 21, 2011 2:27 AM
    Friday, September 16, 2011 8:34 AM