Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
unable to read tables without schema prefix

Answered unable to read tables without schema prefix

  • Tuesday, February 28, 2012 8:38 PM
     
     
    Sign In to Vote
    0

    Hello

    In my SQL Server 2000 I have user "x". The user has no default_schema set.
    In my database all the tables are like "x.mytable"

    If I connect to the db as "x" and try

    select * from mytable

    I get an error "Invalid object name", I have to write it this way

    select * from x.mytable

    I need to be able to use the first syntax and that one worked until I started to mess with server roles for the user. Now I can't get it back to where it was. Any suggestions?

     

All Replies

  • Tuesday, February 28, 2012 10:51 PM
     
     
    Sign In to Vote
    0
    Set the default schema for user x to schema x.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

     
  • Tuesday, February 28, 2012 11:44 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    I need to be able to use the first syntax and that one worked until I started to mess with server roles for the user. Now I can't get it back to where it was. Any suggestions?

     

    Did you perhaps add the login to the sysadmin server role?  Sysamin role members are the dbo user in all databases with default schema dbo.  Consequently, sysadmin role members must schema-qualify objects not in the dbo schema.

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Marked As Answer by gunde Wednesday, February 29, 2012 7:42 AM
    •  
     
  • Wednesday, February 29, 2012 7:41 AM
     
     
    Sign In to Vote
    0

    BINGO!

    Not only was i sysadmin, I also had the user checked as db_owner on another db. Thanks for pointing me in the right direction.

     
  • Wednesday, February 29, 2012 1:34 PM
    Moderator
     
     
    Sign In to Vote
    0

    I'm glad I was able to put you on the right path.

    Unlike sysadmin role members, members of the db_owner fixed database role are not the dbo of the database, although the user has similar permissions.  Schema-qualification is not required for db_owner.  However, it is a best practice for all users to schema-qualify objects and assign only those roles/permissions needed.

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/