SQL Server 2005 implements the ANSI concept for schemas. Schemas are container objects that allow you to group database objects. Schemas have a large impact on how you reference database objects. In SQL Server 2005, a database object is referenced by a four-part name with the structure
You can create database schemas using the CREATE SCHEMA statement. When you create a schema, you can create database objects and assign permissions in the same transaction that calls the CREATE SCHEMA statement. The following sample (included in the sample files as ManagingAccessToSchemas01.sql) creates a schema called Accounting, specifies Peter as the schema's owner, and creates a table called Invoices. The sample also grants select permission to the public database role
-- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Create the schema Accounting with Peter as owner. CREATE SCHEMA Accounting AUTHORIZATION Peter; GO -- Create the table Invoices in the Accounting schema. CREATE TABLE Accounting.Invoices ( InvoiceID int, InvoiceDate smalldatetime, ClientID int); GO -- Grant SELECT permission on the new table to the public role. GRANT SELECT ON Accounting.Invoices TO public; GO -- Insert a row of data into the new table. -- Note the two-part name that we use to refer -- to the table in the current database. INSERT INTO Accounting.Invoices VALUES (101,getdate(),102);
You can drop a schema by using the DROP SCHEMA statement. SQL Server 2005 does not allow you to drop a schema if the schema owns objects. You can obtain information about schemas by querying the sys.schemas catalog view. The following sample queries the sys.schemas catalog view in order to obtain schema information:
SELECT * FROM sys.schemas;
The following code (included in the sample files as ManagingAccessToSchemas02.sql) demonstrates how to drop an existing schema by quering the objects owned by the schema and dropping these objects first:
-- Change the connection context to the database AdventureWorks. USE AdventureWorks GO -- Retieve informatiomn about the Accounting schema. SELECT s.name AS 'Schema', o.name AS 'Object' FROM sys.schemas s INNER JOIN sys.objects o ON s.schema_id=o.schema_id WHERE s.name='Accounting'; GO -- Drop the table Invoices from the Accounting schema. DROP TABLE Accounting.Invoices; GO -- Drop the Accounting schema. DROP SCHEMA Accounting;
Introducing User-Schema Separation
One of the advantages of schemas is the separation of users from objects. In SQL Server 2005, all objects belong to schemas, so you can modify and remove database users without any impact on database objects or on references to these objects from database applications. This abstraction allows you to have objects owned by multiple users since you can create a schema owned by a database role.
Using the Default Schema
When an application references a database object without the qualified schema, SQL Server tries to find the object in the user's default schema. If the object is not on the default schema, SQL Server tries to find the object in the dbo schema. The following sample (included in the sample files as ManagingAccessToSchemas03.sql) shows how to create a schema and assign it as a default schema for a user.
-- Create a SQL Server login in this SQL Server instance. CREATE LOGIN Sara WITH PASSWORD='TUT87rr$$'; GO -- Change the connection context to the database AdventureWorks. USE AdventureWorks; GO -- Create the user Sara in the AdventureWorks database -- and map the user to the login Sara CREATE USER Sara FOR LOGIN Sara; GO -- Create the schema Marketing, owned by Peter. CREATE SCHEMA Marketing AUTHORIZATION Peter; GO -- Create the table Campaigns in the newly created schema. CREATE TABLE Marketing.Campaigns ( CampaignID int, CampaignDate smalldatetime, Description varchar (max)); GO -- Grant SELECT permission to Sara on the new table. GRANT SELECT ON Marketing.Campaigns TO Sara; GO -- Declare the Marketing schema as the default schema for Sara ALTER USER Sara WITH DEFAULT_SCHEMA=Marketing;