The Database Engine uses schemas in its security model to simplify the relationship between users and objects, and thus schemas have a very big impact on how you interact with the Database Engine. This section describes the role of schemas in Database Engine security. The first subsection describes the relationship between schemas and users; the second subsection discusses all three Transact-SQL statements related to schema creation and modification.
User-Schema Separation
A schema is a collection of database objects that is owned by a single person and forms a single namespace. (Two tables in the same schema cannot have the same name.) The Database Engine supports named schemas using the notion of a principal. As you already know, a principal can be either of the following:
- An indivisible principal
- A group principal
An indivisible principal represents a single user, such as a login or Windows user account. A group principal can be a group of users, such as a role or Windows group. Principals are ownerships of schemas, but the ownership of a schema can be transferred easily to another principal and without changing the schema name.
The separation of database users from schemas provides significant benefits, such as:
- One principal can own several schemas.
- Several indivisible principals can own a single schema via membership in roles or Windows groups.
- Dropping a database user does not require the renaming of objects contained by that user’s schema.
Each database has a default schema, which is used to resolve the names of objects that are referred to without their fully qualified names. The default schema specifies the first schema that will be searched by the database server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER statement. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema. (All default schemas are described in detail in the section “Default Database Schemas” later in this chapter.)
