Solved

SQL Server:  Tables Named the Same With Different Schemas in Same Database

Posted on 2014-02-05
5
345 Views
Last Modified: 2014-02-10
If two tables are named the same in a database with different owners, and a schema is not specified when referencing that table, is there an order of precedence that is used in referencing the tables?

For example:

master.dbo.IPLookup
guest.dbo.IPLookup

SELECT * FROM IPLookup

I have done some preliminary testing and it appears to go to the table owned by the default schema used by the current login, for a login with a single schema.

And I thought I recalled the ability for specifying multiple default schemas in SSMS, so I looked in SSMS and attempted to assign multiple default schemas to myself.  I went to the Logon Properties window for my account, then the User Mapping tab.  I selected the appropriate database, and then clicked the lookup button under the Default Schema option.  The Select Schema window appeared, and I clicked the Choose Object button.  I DID then choose multiple schemas (db_datareader and db_datawriter).  Clicked OK, then OK.  Back on the Logon Properties, only the first selected schema is listed under the Default Schema.  So it appears to allow you to select more than one, but only actually accepts one?

In the example where there were tables owned by the owner and guest schemas above, what table would it go to if the logon did not specify a schema when refernecing the object and had a default schema of db_datareader?

Thanks In Advance
0
Comment
Question by:mjs082969
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39836499
I want to address a couple of things here.

First:  If there is ever any possibility of duplicate object names in different schemas, allowing defaults to take over is dangerous.  If I have mydb.dbo.table1 and mydb.schema1.table1 and when we do 'select * from table1', it will go to the current user's default schema to get the table.

If the user's default schema changes from dbo to schema1, you're now selecting data from a completely different table.  If you ever find yourself in this sort of setup, you will always want to fully qualify your database object name in order to ensure you always address the correct object.   (Some may say you always want to fully qualify your database objects all the time, possibly, that's an argument for another time).

Note (see docs below) that a user in the sysadmin server role can only have dbo as default.

However, you mention db_datareader and db_datawriter as schemas.  They are not schemas.  A Schema is a container that contains database objects.

db_datareader and db_datawriter are fixed database roles, allowing a user to have read access or write access to tables within the database.

A role is a permission set granted to a user.
A schema is a container that contains database objects.

Some references from the documentation:

ALTER USER statement: http://msdn.microsoft.com/en-us/library/ms176060.aspx
USER / Schema separation: http://technet.microsoft.com/en-us/library/ms190387%28v=sql.105%29.aspx
db_datareader: http://technet.microsoft.com/en-us/library/ms188629%28v=sql.90%29.aspx
Database level roles: http://technet.microsoft.com/en-us/library/ms189121%28v=sql.90%29.aspx
Ownership and User/Schema Separation in SQL Server: http://msdn.microsoft.com/en-us/library/bb669061%28v=vs.110%29.aspx

This one has the key piece:  When database objects are referenced by using a one-part name, SQL Server first looks in the user's default schema. If the object is not found there, SQL Server looks next in the dbo schema. If the object is not in the dbo schema, an error is returned.

Also I believe (I haven't found anything to the contrary) that unless you have active synonyms in place, you only refer to local objects (current database) when using just the table name in a select statement.

The example you give refers to tables in two different databases.

See CREATE SYNONYM: http://technet.microsoft.com/en-us/library/ms177544.aspx
0
 

Author Comment

by:mjs082969
ID: 39836650
So why does Microsoft label that column 'Default Schema'?

(See attached)
Login-Properties.jpg
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 39836813
>> If two tables are named the same in a database with different owners, and a schema is not specified when referencing that table, is there an order of precedence that is used in referencing the tables?

I have done some preliminary testing and it appears to go to the table owned by the default schema used by the current login, for a login with a single schema.<<


SQL first uses the default schema for that user (not login, since a login doesn't have a default schema).  If the object is not found under that default schema, SQL then defaults to a schema of "dbo".

A user can only have a single default schema, not a list of default schemas to check in order.

If a user has a default schema of "db_datareader" -- which, except for user "db_datareader" should never be the case -- then the statement:

SELECT * FROM IPLookup

would look for "db_datareader.IPLookup" and if that is not found, then "dbo.IPLookup".
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 39836814
In SQL Server 2000, a schema and a user were the same thing.  In SQL Server 2005, this was changed.  See: http://technet.microsoft.com/en-us/library/ms190387%28v=sql.105%29.aspx

The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

I have been trying to find anywhere in the SQL Server documentation why the fixed roles have similarly named schemas.

While I can't find anything in the documentation to support it, it appears that as a backward compatibility feature in SQL Server 2005, fixed database roles were also created as schemas of the same name.

The appear to be empty and it is even suggested in on of the posts below that they can be dropped.

I did find a couple of blogs / forums talking about it but that's all I have found to date:

http://tonybigworld.wordpress.com/2012/09/28/sql-server-2008-schema-logins-and-roles/
http://dbaspot.com/sqlserver-server/401333-db_datareader-schema.html

If you ran this query:

select * 
from sys.tables 
where schema_id in 
(select schema_id from sys.schemas where name = 'db_datareader');

Open in new window


You will probably find that there are no objects in that schema.

A user can own multiple schemas - but each user can have only one default schema.
0
 

Author Comment

by:mjs082969
ID: 39848385
Thank you both, I found both of your responses helpful and will split the points.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question