In a previous post we discussed the four part name of a SQL Server object that included the SCHEMA as part of its naming convention starting in 2005. In this post, we will take a closer look at working with Schemas.

What is a Schema?

This is one of those terms in the IT world that actually has several definitions depending on who you ask. For a developer, it is usually meant as the definition of an object. For example, in SQL Server, it would indicate how a table was defined. How many columns were in the table, the data types of each column, and any possible constraints on the table.

However, for a database administrator, the term schema means something else entirely. It is actually a unit of organization and security. Schemas could be used to provide logical separation of tables, views, or procedures based on department, location, or use.

They could also be used to provide an extra level of security. Instead of having to manage permissions at each table, view, or stored procedure, administrators could place permissions at the schema level to provide or deny access to those objects underneath that schema.

Create a Schema and Assign Permissions

In the example below, we create a new schema named Sales and allowing the Bjorn identity to be the owner of the Sales schema.  Next, we GRANT the SELECT permission to Jane and then DENY the SELECT permission from Sara. This will Grant or Deny the SELECT permission to ALL the tables and views that are being organized under the Sales schema.

CREATE SCHEMA Sales AUTHORIZATION Bjorn
	GRANT SELECT ON SCHEMA:: Sales to Jane
	DENY SELECT ON SCHEMA:: Sales to Sara

Creating a Schema with a Table

Another way of creating a schema in SQL is to streamline the creation while also creating a table that will be stored underneath that schema. In this following example, we create the Production schema while also creating a simple Products table.

CREATE SCHEMA Production AUTHORIZATION Bjorn
CREATE TABLE Products
(ProductID int,
 ProductName varchar(15))

Moving a Table to Another Schema

Another issue we may run into is if we want to move a table from one schema to another. Normally, this is not necessary if we started with a good database design. But in case a table needs to be moved, it needs to be done as part of an ALTER SCHEMA statement. In this example, we will alter the HR schema and then TRANSFER the TestTable from the dbo schema into the HR schema.

 ALTER SCHEMA HR TRANSFER dbo.TestTable

Dropping a Schema

Finally, if you no longer want to use a schema you can use the DROP SCHEMA statement. However, you need to make sure you transfer all the objects to another schema first.

DROP SCHEMA Production;