SQL Server 2014 - Database Schemas

In SQL Server, a database schema faciliates security management. A schema assists in defining who can access each database object.

A database schema can also act as a namespace. This prevents name clashes of objects from different schemas.

What is a Database Schema?

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Creating a Database Schema

To create a database schema in SQL Server 2014:

  1. From the Object Explorer, navigate to Security and open the Schemas node.
  2. Right click on Schemas and select New Schema.... Like this:
    Creating a database schema in SQL Server 2014 Management Studio - 1
  3. Complete the details in the General tab for the new schema. In this example, the schema name is Maintenance and the schema owner is db_owner (you can also use the Search function to find an owner).
    Creating a database schema in SQL Server 2014 Management Studio - 2
  4. Click on Permissions to add users to the schema
  5. To add users, use the Search... button, then Browse...:

    Creating a database schema in SQL Server 2014 Management Studio - 4
  6. Select the user and click OK:
    Creating a database schema in SQL Server 2014 Management Studio- 5
  7. Once the user/s have been added, set their permissions. In my example, I granted the Zoidberg user Alter, Delete, Insert, Select, and Update permissions:
    Creating a database schema in SQL Server 2014 Management Studio- 6
  8. Add any extended properties via the Extended Properties tab (I left this blank)
  9. Click OK.

Add a Table to the New Schema

Now that we have a new schema, we can add objects such as tables, views, and stored procedures to it. For example, we could transfer one or more tables that we created in the earlier lesson to the new schema.

When we created that table (called "Tasks"), it was created in the default database schema ("dbo"). We know this because it appears in our object browser as dbo.Tasks.

To transfer the "Tasks" table to the "Maintenance" schema, do the following:

  1. In Object Explorer, ensure that you're working with the TaskTracker database, then right click on the Tasks table and select Design:
    Changing database schema for a table in SQL Server Management Studio
  2. From Design view, the Properties window should appear to the right. If not, press F4 to display the Properties window.
  3. From the Properties window, change the schema to the desired schema:
    Changing database schema for a table in SQL Server Management Studio
  4. You may get a warning that says Changing the schema of this object will result in all current permissions being dropped. Are you sure you want to proceed?. Click OK to proceed.
  5. Close Design view by right clicking the x on the tab.
  6. Click Yes when prompted to save

Your table has now been transferred to the "Maintenance" schema.

Confirm your Change

To confirm the change:

  1. Refresh the Object Explorer by right-clicking on Tables and clicking Refresh.
  2. You will now see that Object Explorer displays the new schema for the table (i.e. Maintenance.Tasks):
    Screenshot of the table in Object Browser