Schema separation
In Microsoft SQL Server, a schema is a collection of database objects that are owned by a single principal and form a single namespace.
All objects within a schema must be uniquely named and a schema itself must be uniquely named in the database catalog. SQL Server (since version 2005) breaks the link between users and schemas. In other words, users do not own objects; schemas own objects, and principals own schemas.
Users can now have a default schema assigned using the DEFAULT_SCHEMA
option from the CREATE USER
and ALTER USER
commands. If a default schema is not supplied for a user, then the dbo will be used as the default schema.
If a user from a different default schema needs to access objects in another schema, then the user will need to type a full name. For example, Denis needs to query the Contact
tables in the Person
schema, but he is in Sales
. To resolve this, he would type:
SELECT * FROM Person.Contact
Figure 8-3. Accessing schema separated objects
Keep in mind that...