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

System Tables Replaced with Catalog Views and Functions

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.

Note:
Users connecting through a Windows-authenticated group will not have a default schema association. If such a user creates an object that is not qualified with a schema, a new schema is created, its name is set to the current user's name, and the table object is created in this new user-named namespace.

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

Caution:
You must use the new catalog views in any database in which any of the following DDL statements has ever been used: CREATE/ALTER/DROP SCHEMA; CREATE/ALTER/DROP USER; CREATE/ALTER/DROP ROLE; CREATE/ALTER/DROP APPROLE; ALTER AUTHORIZATION.

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:

A. Creating a schema and assigning ownership to a user

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

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

C. Changing ownership of a schema

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

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

Concepts

Other Resources

Help and Information