The importance of fully qualifying database objects in Queries, Functions etc.

[Patrick Wolf]

Environment: WinXP / SQL Server 2000 (Integrated Authentication) /
Access 2003 / ADP Access Project
-------------------------------------------------------------------------------------------------------------

Hi,

-the people developing the database are Administrators who are seemingly
automatically in the db_owners group.
-the people using the database are in all kind of groups and will not be
creating queries, tables, stored procedures etc. but just use an Access
Project which in turn will access SQL Server.

What is the importance in this setup of fully qualifying database objects
with owner.table (dbo.table) in queries, views, functions etc.?
What problems could we run into in the future if we don't do it now (at the
moment it is working fine for both developers and users)?.

Thank you very much for your suggestions
Patrick

 

[Sylvain Lafontaine]

Even with Administrators, you should always use dbo. prefix for a (slight)
better performance: the SQL-Server will recompile the stored procedures in
its cache much less often and it will take a little less time to compile.

Also and unless you are able to read the futur, you never know when you will
have to crank up the security of your system and start using things such as
user defined SPs and Views for filtering and limiting the access to the data
in the tables.

 

[Patrick Wolf]

Thank you very much Sylvain.

What would happen if I just use table instead of dbo.table and user start
creating their own views and procedures? Would it become a mess because the
database would give an error saying that objects names have become ambigous?
Because the object exists twice just with different user names?

Thanks
Patrick

 

[Sylvain Lafontaine]

No, the system won't start throwing out errors and will use the objects with
the nearest ownership to your users (but that doesn't mean that your users
will get exactly what they want (or think they want). BOL have all the
details about the name resolution procedure.

However, with or without « dbo. », I can tell you with certainty that your
database will become a mess if you let your users creating their own views
and procedures.

Finally, for a topic like this, you should ask your question to a SQL-Server
newsgroup; if you want to have very appropriate answers.

 

[Patrick Wolf]

A question in the same area.

If I open an access database in a user context which is not dbo all table
names become "tableX(dbo)".
Now I have trouble with Subforms which display a table directly (without
using a form).

Setting the SourceObject of a Subform used to be:
frmSub.SourceObject = "TableX"
Now since all tables are called tableX(dbo)
frmSub.SourceObject = "TableX" gives an error not found
frmSub.SourceObject = "dbo.TableX" gives the error that this is against the
naming conventions
frmSub.SourceObject = "dbo..TableX" gives an error
frmSub.SourceObject = "TableX(dbo)" gives no error but doesnt display the
table in the subform either.

What to do? Thanks for any help
Patrick

 

[Patrick Wolf]

Thanks I wasn't planning on doing this