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

By:   |   Updated: 2023-01-09   |   Comments   |   Related: More > 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];
Successful Execution

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.

Msg 229, Level 14, State 5, Procedure Rentals.Proc1, Line 1 [Batch Start Line 2]
The EXECUTE permission was denied on the object 'Proc1', database 'SkiShop', schema 'Rentals'.

Msg 229, Level 14, State 5, Procedure Sales.Proc1, Line 1 [Batch Start Line 2]
The EXECUTE permission was denied on the object 'Proc1', database 'SkiShop', schema 'Sales'.

Msg 229, Level 14, State 5, Procedure Service.Proc1, Line 1 [Batch Start Line 2]
The EXECUTE permission was denied on the object 'Proc1', database 'SkiShop', schema 'Service'.

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

Msg 229, Level 14, State 5, Line 8
The SELECT permission was denied on the object 'TableA', database 'SkiShop', schema 'Rentals'.

Msg 229, Level 14, State 5, Line 9
The SELECT permission was denied on the object 'TableA', database 'SkiShop', schema 'Sales'.

Msg 229, Level 14, State 5, Line 10
The SELECT permission was denied on the object 'TableA', database 'SkiShop', schema 'Service'.
Errors Returned
Next Steps

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






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin 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. He has spoken at Boston and Providence S

View all my tips


Article Last Updated: 2023-01-09

Comments For This Article

















get free sql tips
agree to terms