Security: Schemas

In the database world, the word 'Schema' is associated with the description of the tables and the relations in the database. However when Microsoft launched SQL Server 2005, they introduced a securable called Schema which created another meaning for the word 'Schema' in the database world. A Schema is a container of database objects (e.g. tables, views, stored procedures etc) and it is used to define the namespace for the objects. It is used to make management of objects easier for a database users. Schemas and users are separate entities and a schema can be owned by any user or database role.

In previous versions of SQL Server prior to SQL Server 2005, the four-part name for database objects included the owner name where as SQL Server 2005 and 2008 has schema name replacing owner name in the naming scheme.

Scenario 1 - SQL Server 2000
     A user named Fred created objects in the 'Product' database. Each object has the following four-part name :
          [Server Name]. Product.Fred.[Object Name]
Scenario 2 - SQL Server 2005
     Objects need to be part of a schema and since Fred does not want to use the 'dbo' schema he created a schema called 'work'. Each object has the following four-part name :
          [Server Name]. Product.work.[Object Name]

This is beneficial since changing user names or removing users does not require manually transferring each object the user owned to another user. In Scenario 1,if the Fred user is deleted then no one will be able to access any of the objects that he created unless each object is transferred to another user. However in scenario 2, the user Fred can be deleted once the ownership of the schema is transferred to a next user.

A default schema is assigned to each database user for each database that the user can access. This allows users to access objects in the default schema without having to specify the schema name. For example, Fred who has a default schema of 'work' can select data from the table work.Company by specifying Customer in his query instead of work.Company. If you need to access another schema, a two-part identifier (schema_name.object_name) will be required.

Syntax to create a schema:
     CREATE SCHEMA [schema_name] AUTHORIZATION [owner_name]
     e.g. CREATE SCHEMA work AUTHORIZATION Fred

The ALTER SCHEMA is used to transfer a securable between schemas. Syntax:
     ALTER SCHEMA [schema_name] TRANSFER [securable_name]
     e.g. ALTER SCHEMA work TRANSFER dbo.customers
     Note: the securable_name can be one-part or two-part named

Syntax to remove a schema:
     DROP SCHEMA [schema_name]