SQL Create Schema to Organize Database Objects, Assign Permissions and Easier Management

By: Joe Gavin   |   Updated: 2023-01-09   |   Comments   |   Related: > Database Design

Problem

I have seen that when you create or access SQL Server objects you can also provide a schema name for the object. What is a schema and how are these used in Microsoft SQL Server?

Solution

In this tutorial, we'll look at the definition of a schema, take a brief look at the history of schemas, look at built-in schemas, reasons for using schemas, and an example of creating and assigning permissions to schemas.

What is a Schema?

A schema in SQL Server is simply a logical group of objects the current database. They are not to be confused with the definition of a schema in Oracle which is analogous to a database in SQL Server.

History of Schemas

Through SQL Server 2000, an object was owned by the user that created it. This meant that in order to drop a user in a database you would need to reassign any objects they created to another user. As of SQL Server 2005, schemas are a way to separate an object's creator from the object itself.

Built-In Schemas

There are four pre-defined built-in schemas that are created when you install a SQL Server:

• dbo
  • Default schema
  • Assumed schema if no schema name is specified
    • Querying [TableName] vs. [SchemaName].[TableName]
• guest
  • Owned by Guest user
    • Disabled by default
  • Rarely, if ever used
• INFORMATION_SCHEMA
  • Schema for SQL Server metadata views
• sys
  • Object information
  • Running query information
  • In-memory Dynamic Management Views (DMVs)

Why use Schemas?

• Flexibility to organize database objects
• Multiple users can have permissions on a schema
• Users can be dropped without affecting objects or schemas
• Allows more than one object to have the same name such as in data warehouse with data from multiple data sources and identical table names, the schema adds a level of distinction.

What is an Example of Using Schemas?

Schemas are an easy way to organize objects in a database. For example, say you have a database for a ski shop that sells, rents, and repairs skis and it has the following departments:

• Parts
• Rentals
• Sales
• Service

Each department can have its own schema with users assigned permissions on schemas.

Create Schema Statement

Here's the full T-SQL CREATE SCHEMA syntax:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]  
<schema_name_clause> ::=  
    {  
    schema_name  
    | AUTHORIZATION owner_name  
    | schema_name AUTHORIZATION owner_name  
    }  
<schema_element> ::=   
    {   
        table_definition | view_definition | grant_statement |   
        revoke_statement | deny_statement   
    }

Using our ski store database, we're going to:

• Create a new SQL database
• Create a database user called User1
• Create a schema for the Parts, Rentals, Sales, and Service Departments
• Create one new table in each schema
• Create a simple stored procedure that will query all records in its respective sample table
• Grant select and execute permissions on the Parts schema to User1

Here are the SQL statements:

-- 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 table statement
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 procedures
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 to users
GRANT SELECT ON SCHEMA::[Parts] TO [User1];
GRANT EXECUTE ON SCHEMA::[Parts] TO [User1];
GO

Next, we'll connect as User1 and execute the [Parts].[Proc1] stored procedure in the SkiShop database.

USE [SkiShop];
GO
 
EXEC [Parts].[Proc1];

It executes and returns zero records of course as it's an empty table but we see it executed. We've seen that User1 has select and execute rights on Parts.TableA that were granted to the Parts schema.

Now, let's see what happens on the other schemas and tables by attempting to execute the other three stored procedures or select from the tables in the other three schemas where User1 has not been granted permissions.

USE [SkiShop];
GO
 
EXEC [Rentals].[Proc1];
EXEC [Sales].[Proc1];
EXEC [Service].[Proc1];
 
SELECT * FROM [Rentals].[TableA]
SELECT * FROM [Sales].[TableA];
SELECT * FROM [Service].[TableA];

We see the following errors stating we do not have permission to execute the stored procedures.

And attempting to directly query the tables yields these errors stating we do not have permission to select from the tables either.

Next Steps

We've seen some basics of SQL Server schemas and here are some schema related tips with more information:

• Understanding SQL Server Schemas
• SQL Server Schema Binding and Indexed Views
• Using Schema Binding to Improve SQL Server UDF Performance
• Default Schema for Windows Group in SQL Server
• SQL Server Replication Between Different Schemas
• Script to Set the SQL Server Database Default Schema For All Users
• Using the OBJECT_SCHEMA_NAME Function to Return the SQL Server Schema Name
• Script to Move all objects to a New Schema for SQL Server
• SQL Server Database Schema Synchronization via SQLPackage.exe and PowerShell
• Grant limited permissions to create SQL Server views in another schema Part 1
• Understanding GRANT, DENY, and REVOKE in SQL Server

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips