The complete name of a relational database management system (RDBMS) object consists of four parts. In SQL Server, the complete name form is server.database.schema.object. Objects also have owners, and owners are database users and roles. However, the owners are hidden; you typically never refer to an object owner in the code that deals with data, whereas you intensively use schemas. Schemas are more than just namespaces for database objects; they are securables as well. Instead of giving permissions to specific database objects, a DBA can give users permissions to schemas. For example, granting the Execute permission to the Sales schema gives the grantees the Execute permission on all objects in this schema for which this permission makes sense, such as stored procedures and functions. Therefore, you should plan your schemas carefully.

When you refer to database objects in your code, you should always use a two-part name, in the form schema.object. You don't want to use more than two parts because you don't want to make your application dependent on a specific server or database name. However, because of the way SQL Server handles name resolution, you should not use a single-part name either.

In SQL Server, every user has a default schema. You can specify the default schema for a user when you create the user. You can change the default schema of a user at any later time. If you do not specify an explicit default schema for a user, the default schema is dbo. This schema exists in all SQL Server databases and is owned by the dbo user. Thus, SQL Server first checks for a partially specified object name if the object exists in the user's default schema and then checks the dbo schema. To fully understand this behavior, let's work through the following code; note that this code assumes you are working in the dbo database user context because it uses the EXECUTE AS command to impersonate a database user and you must have the correct permission to use this command.

The first part of the code creates a demo database and another login called LoginB. Note that a login called LoginA should already exist at this point:

USE master; 
IF DB_ID(N'SQLDevGuideDemoDb') IS NULL 
CREATE DATABASE SQLDevGuideDemoDb; 
CREATE LOGIN LoginB WITH password='LB_ComplexPassword'; 
GO 

The next part of the code creates a new schema called Sales in the SQLDevGuideDemoDb demo database, and then two tables with the same name and structure, one in the dbo schema and one in the new Sales schema:

USE SQLDevGuideDemoDb; 
GO 
CREATE SCHEMA Sales; 
GO 
CREATE TABLE dbo.Table1 
(id INT, tableContainer CHAR(5)); 
CREATE TABLE Sales.Table1 
(id INT, tableContainer CHAR(5)); 
GO 

The following two insert statements insert one row into each table. The value of the character column shows the name of the table schema:

INSERT INTO dbo.Table1(id, tableContainer) 
VALUES(1,'dbo'); 
INSERT INTO Sales.Table1(id, tableContainer) 
VALUES(1,'Sales'); 
GO 

The next part of the code creates two database users, one for LoginA and one for LoginB, with the same name as their respective login name. Note that the default schema for user LoginA is dbo, while for LoginB it is Sales. Both users are also granted the permission to select data from both demo tables:

CREATE USER LoginA FOR LOGIN LoginA; 
GO 
CREATE USER LoginB FOR LOGIN LoginB 
 WITH DEFAULT_SCHEMA = Sales; 
GO 
GRANT SELECT ON dbo.Table1 TO LoginA; 
GRANT SELECT ON Sales.Table1 TO LoginA; 
GRANT SELECT ON dbo.Table1 TO LoginB; 
GRANT SELECT ON Sales.Table1 TO LoginB; 
GO 

Next, you impersonate LoginA. In a query, you refer to the table you are reading with a single-part name only (that is, with the table name only):

EXECUTE AS USER='LoginA'; 
SELECT USER_NAME() AS WhoAmI, 
id, 
tableContainer 
FROM Table1; 
REVERT; 
GO 

Here are the results:

    WhoAmI  id  tableContainer
    ------  --  --------------
    LoginA   1  dbo

You can see that you read from the dbo.Table1 table. Repeat the same thing for the database user LoginB:

EXECUTE AS USER='LoginB'; 
SELECT USER_NAME() AS WhoAmI, 
id, 
tableContainer 
FROM Table1; 
REVERT; 
GO 

This time the results show that you read the data from the Sale.Table1 table:

    WhoAmI  id  tableContainer
    ------  --  --------------
    LoginB   1  Sales

Now you drop the Sales.Table1 table. Then you impersonate user LoginB again, and read from the table using the table name only:

DROP TABLE Sales.table1; 
GO 
EXECUTE AS USER='LoginB'; 
SELECT USER_NAME() AS WhoAmI, 
id, 
tableContainer 
FROM Table1; 
REVERT; 
GO 

Here are the results:

    WhoAmI  id  tableContainer
    ------  --  --------------
    LoginA   1  dbo

Now that you know how schemas work, you should be able to understand the following guidelines for managing schemas:

• You should group objects in schemas based on application-access requirements. Classify applications by access requirements and then create appropriate schemas. For example, if an application module deals with sales data, create a Sales schema to serve as a container for all database objects that pertain to sales.
• Typically, you can map end users to application modules. You should specify the appropriate default schemas for database users and roles. For example, you should specify Sales as the default schema for users in the Sales department.
• Because SQL Server uses a permissions hierarchy, you can manage permissions efficiently if you set up appropriate schemas. For example, you can give permissions on data to sales-department users quickly by giving them appropriate permissions on the Sales schema. Later, you can define exceptions by denying permissions to some users on the objects contained in the Sales schema.
• You should use either the dbo user or database roles as the owners of schemas and objects. This way, you can drop a database user without worrying about orphaned objects.
• Although you set appropriate default schemas for users, you should still always refer to database objects by using two-part names. With this strategy, you can avoid confusion in your application if the default schema for a user changes, or if an object from the user's default schema is dropped and an object with the same name exists in the dbo schema (as you saw in the code example).
• You can use schemas to control development environments as well. You can identify different developer groups based on application requirements and then map those groups to schemas.
• In SQL Server, you can control permissions on schemas and objects with a lot of precision. For example, giving developers permission to create objects does not imply that they can create objects in all schemas. On the contrary, developers must have an ALTER or CONTROL schema permission on every schema they want to modify by creating, altering, or dropping objects contained in that schema.
• You can move objects between schemas by using the ALTER SCHEMA command.
• Your documentation should include schema information.