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.
Note
Database schemas are different from XML schemas. For more information about XML schemas, see Managing XML Schema Collections on the Server.
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:
SQL Server 2000 system table | SQL Server 2005 catalog view |
---|---|
Sysusers |
|
Syslogins |
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
Warning
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.
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:
For information on | See |
---|---|
General schema metadata |
|
Information schema views |
|
Column definitions returned by the INFORMATION_SCHEMA.SCHEMATA view |
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
Release | History |
---|---|
12 December 2006 |
|
17 July 2006 |
|