In Microsoft SQL Server, when creating or accessing database objects, you have the option to provide a schema name for the object. But what exactly is a schema and how is it used in SQL Server?

What is a Schema?

A schema in SQL Server is a logical group of objects within a database. It is important to note that a schema in SQL Server is not the same as a schema in Oracle, which is analogous to a database in SQL Server.

History of Schemas

Prior to SQL Server 2005, an object in SQL Server was owned by the user who created it. This meant that if you wanted to drop a user from a database, you would need to reassign any objects they created to another user. However, starting from SQL Server 2005, schemas were introduced as a way to separate an object’s creator from the object itself.

Built-In Schemas

When you install SQL Server, four pre-defined built-in schemas are created:

• dbo: Default schema assumed if no schema name is specified.
• guest: Owned by the Guest user. Disabled by default and rarely used.
• INFORMATION_SCHEMA: Schema for SQL Server metadata views.
• sys: Schema for object information, running query information, and in-memory Dynamic Management Views (DMVs).

Reasons for Using Schemas

There are several reasons why you might want to use schemas in SQL Server:

• Flexibility to organize database objects: Schemas allow you to logically group related objects together, making it easier to manage and navigate the database.
• Multiple users with permissions: You can assign permissions to schemas, allowing multiple users to have access to the objects within a schema without granting individual permissions on each object.
• Separation of users and objects: If a user is dropped from the database, the objects they created will remain intact as long as they are assigned to a schema. This provides a level of separation between users and objects.
• Distinct object names: In scenarios such as data warehouses with data from multiple sources, schemas can be used to differentiate objects with the same name.

Example of Using Schemas

Let’s consider an example of a ski shop database that has the following departments: Parts, Rentals, Sales, and Service. We can create a schema for each department and assign permissions to users on these schemas.

Here’s an example of how to create schemas and assign permissions using T-SQL:

-- Create database
CREATE DATABASE [SkiShop];
GO

-- Use the new database
USE [SkiShop];
GO

-- Create database user
CREATE USER [User1] FOR LOGIN [User1];
GO

-- Create schemas
CREATE SCHEMA [Parts];
GO
CREATE SCHEMA [Rentals];
GO
CREATE SCHEMA [Sales];
GO
CREATE SCHEMA [Service];
GO

-- Create tables in each schema
CREATE TABLE [Parts].[TableA]
(
    ID int identity(1, 1) PRIMARY KEY, [Col1] varchar(50)
);
GO

CREATE TABLE [Rentals].[TableA]
(
    ID int identity(1, 1) PRIMARY KEY, [Col1] varchar(50)
);
GO

CREATE TABLE [Sales].[TableA]
(
    ID int identity(1, 1) PRIMARY KEY, [Col1] varchar(50)
);

CREATE TABLE [Service].[TableA]
(
    ID int identity(1, 1) PRIMARY KEY, [Col1] varchar(50)
);
GO

-- Create stored procedures in each schema
CREATE PROCEDURE [Parts].[Proc1]
AS
SELECT * FROM [Parts].[TableA];
GO

CREATE PROCEDURE [Service].[Proc1]
AS
SELECT * FROM [Service].[TableA];
GO

CREATE PROCEDURE [Rentals].[Proc1]
AS
SELECT * FROM [Rentals].[TableA];
GO

CREATE PROCEDURE [Sales].[Proc1]
AS
SELECT * FROM [Sales].[TableA];
GO

-- Grant select and execute permissions on the Parts schema to User1
GRANT SELECT ON SCHEMA::[Parts] TO [User1];
GRANT EXECUTE ON SCHEMA::[Parts] TO [User1];
GO

By granting select and execute permissions on the Parts schema to User1, we can ensure that User1 can access and execute the stored procedures and tables within the Parts schema.

However, if we attempt to execute the stored procedures or select from the tables in the other schemas where User1 has not been granted permissions, we will receive permission errors.

USE [SkiShop];
GO

EXEC [Rentals].[Proc1];
EXEC [Sales].[Proc1];
EXEC [Service].[Proc1];

SELECT * FROM [Rentals].[TableA];
SELECT * FROM [Sales].[TableA];
SELECT * FROM [Service].[TableA];

These errors indicate that User1 does not have permission to execute the stored procedures or select from the tables in the Rentals, Sales, and Service schemas.

By using schemas, we can effectively organize and manage database objects, assign permissions to multiple users, and ensure the separation of users and objects within a SQL Server database.