SQL Server 2005 introduces a new model for managing the fundamental units of data within databases. All data within a database is contained within a Database object. Each Database object contains Schema objects and those Schema objects contain the tables, indexes, views, and other objects that make up the database. So there are three basic levels of scoping and ownership:
Database. Includes all objects defined within a database and is owned by a specific user.
Schema. Includes all objects defined within a schema and is owned by a database-level security principal
Schema-contained object. Refers to any individual table, view, or so forth that is defined in the database and is "owned" by a specific schema.
When you move databases designed for previous versions of SQL Server to SQL Server 2005, this model still applies. In these databases, the dbo schema is the owner of tables, views, and other related objects, and you can extend the database structure by creating and using other schemas as necessary.
Schemas are containers of objects that are used to define the namespaces for objects within databases. They are used to simplify management and create object subsets that can be managed collectively. Schemas are separate from users. Users own schemas and always have a default schema that the server uses when it resolves unqualified objects in queries. This means that the schema name does not need to be specified when accessing objects in the default schema. To access objects in other schemas, a two- or three-part identifier is required. A two-part identifier specifies the schema name and the object name in schema_name.object_name format. A three-part identifier specifies the database name, the schema name, and the object name in database_name.schema_name.object_name format.
Database synonyms can be used to create alternate names so that a user’s default schema contains references to other schema. For example, if you create a synonym for the Customers.Contact table as dbo.Contact, any user with dbo as the default schema could access the table using only the table name. Although synonyms can refer to objects in other databases, including remote instances of SQL Server, synonyms are only valid within the scope of the database in which they are defined. This means that each database can have identically named synonyms, and these synonyms could possibly refer to different objects.
Schemas have many benefits. Because users are no longer the direct owners of objects, removing users from a database is a simpler task; you no longer need to rename objects before dropping the user that created them. Multiple users can own a single schema through membership in a role or Windows group, which makes it easier to manage tables, views, and other database-defined objects. Multiple users can share a single default schema, which makes it easier to grant access to shared objects.
Schemas can be used to scope database access by function, role, or purpose to make it easier to access objects contained in a database. For example, you could have schemas named for each application that accesses the database. In this way, when users of a particular application access the database, their namespace is set appropriately for the objects they routinely access.
Before you create a table, you should carefully consider the schema name. Schema names can be up to 128 characters long. Schema names must begin with an alphabetic character, but they can also contain underscores (_), "at" symbols (@), pound signs (#), and numerals. Schema names must be unique within each database. Different databases, however, can contain like-named schemas; for example, two different databases could each have an Employees schema.
In SQL Server Management Studio, you create a new schema by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
Expand the database’s Security node, and then right-click the Schemas node. From the shortcut menu, choose New Schema to display the Schema - New dialog box shown in Figure 9-1.
On the General page, specify the name of the schema and set the schema owner. To search for an available database-level security principal to use as the owner, click Search to display the Search Roles And Users dialog box, and then click Browse to open the Browse For Objects dialog box. Select the user or role to act as the schema owner, and then click OK twice.
Note
The schema owner can be any database-level security principal (database user, database role, or application role). Although the schema owner can own other schemas, the owner cannot use this schema as the default schema. If the schema owner is set as a role or Windows group, multiple users will own the schema.
Click OK to create the schema.
The Transact-SQL command for creating schemas is CREATE SCHEMA. Example 9-1 shows the syntax and usage for this command. The schema_element for the command allows you to use CREATE TABLE, CREATE VIEW, GRANT, REVOKE, and DENY statements to define tables, views, and permissions that should be created and contained within the schema you are defining.
Example 9-1. CREATE SCHEMA Syntax and Usage
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 }Usage
CREATE SCHEMA Employees AUTHORIZATION DataTeam
You may need to change the schema ownership or modify its permissions. One of the primary reasons for changing the schema owner is because the owner cannot use the schema as the default schema. You may also want to allow or deny specific permissions on a per user or per role basis. After a schema is created, however, you cannot change the schema name. You must drop the schema and create a new schema with the new name.
In SQL Server Management Studio, you can change the schema owner by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
Expand the database’s Security node, and then Schemas node. Right-click the schema you want to work with. From the shortcut menu, choose Properties to display the Schema Properties dialog box.
To change the schema owner, click Search on the General page to display the Search Roles And Users dialog box, and then click Browse to open the Browse For Objects dialog box. Select the user or role to act as the schema owner, and then click OK twice.
You can manage granular permissions for a schema on the Permissions page in the Schema Properties dialog box. Any user or roles that are directly assigned permissions on the object are listed under Users Or Roles. To configure permissions for a user or role:
Select the Permissions page from the Select A Page list in the Schema Properties dialog box.
Click Add to add specific permissions for users, roles, or both. This opens the Select Users Or Roles dialog box.
Enter the names of the users or roles to add. Separate names with semicolons. You can enter partial names, and then click Check Names to expand the names. To search for names, click Browse.
Select a user or role in the Users Or Roles list box. Use the Permissions For... list box to Allow or Deny permissions as appropriate. Clear both check marks to revoke a previously granted or denied permission.
When you are finished, click OK to assign the permissions.
As discussed previously, schemas are containers for objects, and there are times when you will want to move an object from one container to another. Objects can be moved from one schema to another only within the same database. When you do this, you change the namespace associated with the object, which changes the way the object is queried and accessed.
Moving the object to a new schema also affects permissions on the object. All permissions on the object are dropped when it is moved to a new schema. If the object owner is set to a specific user or role, that user or role will continue to be the owner of the object. If the object owner is set to SCHEMA OWNER, the ownership will remain as SCHEMA OWNER, and after the move, the owner will become the owner of the new schema.
To move objects between schemas, you must have CONTROL permissions on the object and ALTER permissions on the schema to which you are moving the object. If the object has an EXECUTE AS OWNER specification on it, and the owner is set to SCHEMA OWNER, you must also have IMPERSONATION permission on the owner of the target schema.
In SQL Server Management Studio, you can move an object to a new schema by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
Right-click the table, view, or other object you want to move. From the shortcut menu, choose View Dependencies. The Object Dependencies dialog box shows the database objects that must be present for this object to function properly and the objects that depend on the selected object. Use this dialog box to understand any dependencies that may be affected by moving the selected object. Click OK.
Right-click the table, view, or other object, and then select Modify. One of several views displayed in the right pane is the Properties view for the selected object. If this view is not displayed, press F4.
Under Identity, click in the Schema drop-down list and select a new schema to contain the selected object.
Caution
All permissions on the object are dropped immediately and irreversibly if you have previously selected the option Don’t Warn Me Again, Proceed Every Time. If you see the warning prompt, click Yes to continue and move the object to the designated schema, or click No to cancel the move.
The Transact-SQL command for moving objects between schemas is ALTER SCHEMA. Example 9-2 shows the syntax and usage for this command. When you alter schema, make sure you are using the correct database and are not using the master database.
If you no longer need a schema, you can drop it and, in this way, remove it from the database. To drop a schema, you must have CONTROL permission on the schema. Before dropping a schema, you must first move or drop all the objects that it contains. If you try to delete a schema that contains other objects, the drop operation will fail.
In SQL Server Management Studio, you can drop a schema by completing the following steps:
In SQL Server Management Studio, connect to the server instance that contains the database in which you want to work.
In Object Explorer, expand the Databases node, and then select a database and expand the view to show its resource nodes.
Expand the database’s Security and Schema nodes. Right-click the schema you wish to drop. From the shortcut menu, choose Delete. This displays the Delete Object dialog box.
Click OK to confirm the deletion.
The Transact-SQL command for deleting schemas is DROP SCHEMA. Example 9-3 shows the syntax and usage for this command. When you drop a schema, make sure you are using the correct database and are not using the master database.