Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 SQL Select - For schema defined tables
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

9 Posts

Posted - 09/12/2005 :  15:16:05  Show Profile  Reply with Quote

I have a schema XXX. This schema owns a set of tables say XXX.A, XXX.B and XXX.C.

I have a login XXX mapped to the user XXX.

When I connect to the SQL Server using login XXX and execute the query.

Select * from A, it throws be an error saying that"Invalid Object Name A".

Now when I modify the select statement specifying the schema it works. i.e. Select * from XXX.A

In the former case, is it not that SQL Server tries to search for the table in the current user's account and then if it not available it look's in the dbo's account.

HOw can I make a select without specifying the schema ?

I am using SQL Server 2005.

Thanks & Regards

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 09/12/2005 :  22:08:49  Show Profile  Reply with Quote
hmmm, sounds fishy. XXX should not need to qualify the table name (with the owner name) when selecting from their own table. While logged in as XXX, run the following code to confirm your table is owned by XXX and that the table ownerid is the same as the logged in userid (myUserID should be the same as tableOwnerID)

select	user_id() myUserID
	,objectproperty(object_id('XXX.A'), 'ownerid') tableOwnerID

Be One with the Optimizer
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000