User-Schema Separation

Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a database user. You can think of a schema as a container of objects. 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.

For more information on creating a database object schema, see CREATE SCHEMA (Transact-SQL).

New Behavior

In previous versions of SQL Server, database users and schemas were conceptually the same object. Beginning in SQL Server 2005, users and schemas are separate, and schemas serve as containers of objects.

The separation of ownership from schemas has important implications. Database schemas provide more control over the security of database objects in the following ways:

• Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases. For more information, see GRANT Schema Permissions (Transact-SQL) and GRANT Object Permissions (Transact-SQL).
• Ownership of schemas and schema-scoped securables is transferable. For more information, see ALTER AUTHORIZATION (Transact-SQL).
• Objects can be moved between schemas. For more information, see ALTER SCHEMA (Transact-SQL).
• A single schema can contain objects owned by multiple database users.
• Multiple database users can share a single default schema.
• A schema can be owned by any database principal. This includes roles and application roles.
• A database user can be dropped without dropping objects in a corresponding schema.

Database schemas introduce other important changes to security from previous versions:

• Code written for earlier releases of SQL Server may return incorrect results if the code assumes that schemas are equivalent to database users.
• Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.
• Ownership chains and user context switching can behave differently now because users can own more than one schema. For more information about ownership chains, see Ownership Chains and Permissions Hierarchy. For more information on context switching, see Context Switching.
• In SQL Server 2000, database objects were owned by users. The four-part reference to a database object in SQL Server 2000 was [DatabaseServer].[DatabaseName].[ObjectOwner].[DatabaseObject]. Beginning in SQL Server 2005, the four-part reference to a database object is [DatabaseServer].[DatabaseName].[DatabaseSchema].[DatabaseObject].

Object Ownership Changes

The owner property of the following objects references a schema, not a user:

• CREATE TABLE
• ALTER TABLE
• CREATE VIEW
• ALTER VIEW
• CREATE INDEX
• ALTER INDEX
• CREATE FUNCTION
• ALTER FUNCTION
• DROP FUNCTION
• VIEW_TABLE_USAGE
• VIEW_COLUMN_USAGE
• TABLE_CONSTRAINTS
• REFERENTIAL_CONSTRAINTS
• KEY_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• CONSTRAINT_COLUMN_USAGE
• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMNS
• DOMAIN_CONSTRAINTS
• ROUTINE_COLUMNS

For more information about which columns return user metadata versus schema metadata, see the "Schemas Catalog Views and Functions" section below.

System Tables Replaced with Catalog Views and Functions

SQL Server 2005 introduces more than 250 new catalog views, some dealing with the database user and schema objects, which replace SQL Server 2000 system tables. We strongly recommend that you use the new catalog views to access metadata. For more information, see Catalog Views (Transact-SQL).

The following table below shows the mapping between the SQL Server 2000 system tables and the equivalent SQL Server 2005 catalog views:

Default Schemas

To resolve the names of securables that are not fully qualified, SQL Server 2000 used name resolution to check the schema owned by the calling database user and the schema owned by dbo.

In SQL Server 2005, each user can be assigned a default schema. The default schema can be set and changed by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If DEFAULT_SCHEMA is not defined, SQL Server 2005 will assume that the dbo schema is the default schema.

New Data Definition Language (DDL) statements can introduce complexities to system metadata that are not accurately reflected in old system tables such as sysobjects. In this example, the user ID and schema name returned by sysobjects are out of sync, reflecting the distinction between user and schema introduced in SQL Server 2005.

USE tempdb
GO
CREATE LOGIN u1 WITH PASSWORD = 'Mdfjd$sakj943857l7sdfh##30'
CREATE USER u1 WITH DEFAULT_SCHEMA = u1
GO
GRANT CREATE TABLE TO u1 
GO
CREATE SCHEMA sch1
GO
CREATE SCHEMA u1 AUTHORIZATION u1
GO
EXECUTE AS USER = 'u1'
GO
CREATE TABLE t1(c1 int)
GO
REVERT
GO
SELECT user_name(uid) , * FROM sysobjects WHERE name = 't1'
GO

Schemas Catalog Views and Functions

Beginning with SQL Server 2005, schemas are explicit entities reflected in metadata. As a result, schemas can only have one owner, but a single user can own one or many schemas. This complex relationship is not reflected in the SQL Server 2000 system tables, so SQL Server 2005 introduces new catalog views, which accurately reflect the new metadata.

The following table below shows the catalog views, metadata, and functions for schemas in SQL Server 2005:

Examples

A. Creating a schema and assigning ownership to a user

The following example adds a SQL Server login and user called Marjorie and a new schema called Auditing to the AdventureWorks database. Marjorie is assigned as the owner of the Auditing schema.

CREATE LOGIN Marjorie
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';
USE AdventureWorks;
CREATE USER Marjorie FOR LOGIN Marjorie
GO
CREATE SCHEMA Auditing AUTHORIZATION Marjorie;
GO

B. Granting a user rights to another schema

The following example grants a user called Marjorie the SELECT permission on the Purchasing schema in the AdventureWorks database.

USE AdventureWorks;
GO
GRANT SELECT ON SCHEMA::Purchasing TO Marjorie;
GO

C. Changing ownership of a schema

In the following example, a new user, Jon, is created in the AdventureWorks database. Jon is granted ownership of the Auditing schema in the AdventureWorks database. Then the user called Marjorie is dropped from the AdventureWorks database.

USE AdventureWorks;
GO
/* Create a new user in the database */
CREATE LOGIN Jon
    WITH PASSWORD = '1fdKJl3$nlNv3049jsBB';
USE AdventureWorks;
CREATE USER Jon FOR LOGIN Jon
GO
ALTER AUTHORIZATION ON SCHEMA::Auditing TO Jon;
GO
/* Removes the user from the system */
DROP LOGIN Marjorie;
GO
DROP USER Marjorie;
GO

D. Displaying ownership of a schema

The following example displays the owner of the Auditing schema in the AdventureWorks database.

USE AdventureWorks;
GO
/* This method uses the INFORMATION_SCHEMA views */
SELECT * 
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'Auditing';
GO

/* This method uses the sys.schemas catalog and links
   the names of the database users and server logins */
SELECT s.name AS 'Schema Name'
, db.name AS 'Database User Name'
, svr.name AS 'SQL Server Login Name'
FROM sys.schemas s
/* Obtains the name of the database user */
   INNER JOIN sys.database_principals db
      ON s.principal_id = db.principal_id
/* Obtains the name of the server login */
      INNER JOIN sys.server_principals svr
         ON db.sid = svr.sid
WHERE s.name = 'Auditing'
ORDER BY s.name

See Also

Concepts

Permissions Hierarchy
Principals

Other Resources

CREATE SCHEMA (Transact-SQL)
ALTER SCHEMA (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
DROP SCHEMA (Transact-SQL)
sys.schemas (Transact-SQL)
CREATE USER (Transact-SQL)
ALTER USER (Transact-SQL)
Making Schema Changes on Publication Databases

Help and Information

Getting SQL Server 2005 Assistance

Change History