why fully qualified table names for read-only access?

Question

why fully qualified table names for read-only access?

acarbo99

SSC Journeyman

Points: 83
More actions
January 27, 2004 at 11:56 am

#101857

This is probably a very simple question, but I will ask
anyway ...
Why is it necessary to have a fully qualified table name in a select statement issued by a login ID that doesn't own the table (unless the owner is dbo)?
For example, I have a database (sampledb01) with a table named customers. The table was created by (& is "owned" by) loginID01, who has full database owner privs. I created a second id, loginID02, but only gave it public & datareader privs (though I implicitly gave it permission to select from the customers table).
My select statment:
select * from customers
gives an "invalid object name" when run by loginID02, but the statment
select * from loginID01.customers
works just fine
Why is this necessary? And why is it NOT necessary if the table is owned by "dbo"?
TIA,
Adria Carbo

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

See Books OnLine: Database Object Owner. If a third user also created a table named Customers, which would you have the system automagically use?

--Jonathan

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 2

Check this thread. It handles the same "issue" :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=6&messageid=96649

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

acarbo99 SSC Journeyman Points: 83 More actions · Answer 3

Thank you. Both replies have been very helpful.

I have a follow up question though:

What is the benefit for allowing multiple users to create a table of the same name? I would think that would only cause a nightmare of trying to synchronize data.

TIA,

Adria

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

http://www.sqlservercentral.com/columnists/awarren/worstpracticesobjectsnotownedbydbo.asp