What is a Schema in SQL Server 2005?

What is a Schema?

In Microsoft SQL Server 2005, a schema is a collection of objects adhering to the ANSI SQL-92 standard.

The ANSI SQL-92 standard defines a schema as a collection of database objects that are owned by a single principle and form a single namespace.

All objects within a schema must be uniquely named and a schema must be uniquely named in the database catalog. SQL Server 2005 breaks the link between users and schemas, users do not own objects. Schemas own objects and principles own schemas.

A schema can be owned by either a primary or secondary principle, with the term “principle” meaning any SQL Server entity that can access securable objects.

Principle types that can own schemas:

• Primary
  • SQL Server Login
  • Database User
  • Windows Login
• Secondary
  • SQL Server Roles
  • Windows Groups
  • Default Schemas

Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.

——————————————————————————–

SQL Server 2005 Schema Objects

• Tables
• Indexes
• Views
• Synonyms
• Stored Procedures
• Triggers
• Functions
• Assemblies
• Types
• Defaults