SQLServer 2014 - using name without schema qualification

Question

I have table and view with same name: "dbo.Users" (table) and "www.Users" (table). There are select statements all over the code that look like this:

SELECT u_id, u_name from Users WHERE ...

I checked with two different users and it seems that view is used in both cases. What are exact rules governing this case? I tried looking for name binding, name search order and similar, but was unable to find anything conclusive in documentation.

Answer 1

The default schema that is used is governed by the "default schema" property for a database user.

You can read this from the CREATE USER documentation:

The default schema will be the first schema that will be searched by the server when it resolves the names of objects for this database user.[...]

There is a bit more to it when no default schema has been specified for a user:

If the user has a default schema, that default schema will used. If the user does not have a default schema, but the user is a member of a group that has a default schema, the default schema of the group will be used. If the user does not have a default schema, and is a member of more than one group, the default schema for the user will be that of the Windows group with the lowest principal_id and an explicitly set default schema. (It is not possible to explicitly select one of the available default schemas as the preferred schema.) If no default schema can be determined for a user, the dbo schema will be used.

---

To avoid situations like that what you have happening, always be explicit by specifying the schema for the database objects you are referencing in queries.