Implementation of Database Object Schemas

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects.

Schema can be considered as a container that can hold many database objects. Database objects are created under a specific schema. If you don’t define any schema while creating a table then object will be created under default schema “dbo” which has database owner privilege.

Example of schema: For a Sales database, we can create following schemas:

HR – all the human resource related tables can be created under this schema.
Product – Product related tables an be create under this schema
Sales – Sales transactions tables can be created under this schema.

Advantages of using schemas:

·         Protecting database objects from being altered by users without the knowledge of the database owner.

·         Preventing database base objects, independent software vendor (ISV) databases in particular, from ad hoc or incorrect user access leading to poor application performance

·         Bringing related groups of objects (logical entities) together within one physical database to reduce physical database administrative overhead

 

Note: A schema cannot be dropped if it contains any objects. If a DROP SCHEMA statement is executed while it contains objects, the drop operation fails.

Creating schema using SQL Server Management Studio:

1.       In Object Explorer, expand the Databases folder.

2.       Expand the database in which to create the new database schema.

3.       Right-click the Security folder, point to New, and select Schema.

4.       In the Schema - New dialog box, on the General page, enter a name for the new schema in the Schema name box.

5.       In the Schema owner box, enter the name of a database user or role to own the schema. Alternately, click Search to open the Search Roles and Users dialog box.

6.       Click OK.

 

 

Creating schema using T-SQL

USE [DatabaseName]

GO

 

CREATE SCHEMA [SchemaName] AUTHORIZATION [OwnerName]

GO

 

USE [Test]

GO

 

CREATE SCHEMA [HR] AUTHORIZATION [dbo]

GO