SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

imtu_174
Starting Member

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

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
Imtiaz

TG
Flowing Fount of Yak Knowledge

USA
5467 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
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000