Jaa

CREATE SCHEMA (Transact-SQL)

  • Artikkeli

Creates a schema in the current database. The CREATE SCHEMA transaction can also create tables and views within the new schema, and set GRANT, DENY, or REVOKE permissions on those objects.

Topic link iconTransact-SQL Syntax Conventions

Syntax

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 
    }

Arguments

  • schema_name
    Is the name by which the schema is identified within the database.
  • AUTHORIZATION owner_name
    Specifies the name of the database-level principal that will own the schema. This principal may own other schemas, and may not use the current schema as its default schema.
  • table_definition
    Specifies a CREATE TABLE statement that creates a table within the schema. The principal executing this statement must have CREATE TABLE permission on the current database.
  • view_definition
    Specifies a CREATE VIEW statement that creates a view within the schema. The principal executing this statement must have CREATE VIEW permission on the current database.
  • grant_statement
    Specifies a GRANT statement that grants permissions on any securable except the new schema.
  • revoke_statement
    Specifies a REVOKE statement that revokes permissions on any securable except the new schema.
  • deny_statement
    Specifies a DENY statement that denies permissions on any securable except the new schema.

Remarks

Note

Statements that contain CREATE SCHEMA AUTHORIZATION but do not specify a name are permitted for backward compatibility only.

CREATE SCHEMA can create a schema, the tables and views it contains, and GRANT, REVOKE, or DENY permissions on any securable in a single statement. This statement must be executed as a separate batch. Objects created by the CREATE SCHEMA statement are created inside the schema that is being created.

CREATE SCHEMA transactions are atomic. If any error occurs during the execution of a CREATE SCHEMA statement, none of the specified securables are created and no permissions are granted.

Securables to be created by CREATE SCHEMA can be listed in any order, except for views that reference other views. In that case, the referenced view must be created before the view that references it.

Therefore, a GRANT statement can grant permission on an object before the object itself is created, or a CREATE VIEW statement can appear before the CREATE TABLE statements that create the tables referenced by the view. Also, CREATE TABLE statements can declare foreign keys to tables that are defined later in the CREATE SCHEMA statement.

Note

In SQL Server 2005, DENY and REVOKE are supported inside CREATE SCHEMA statements. DENY and REVOKE clauses will be executed in the order in which they appear in the CREATE SCHEMA statement.

The principal that executes CREATE SCHEMA can specify another database principal as the owner of the schema being created. This requires additional permissions, as described in the "Permissions" section later in this topic.

The new schema is owned by one of the following database-level principals: database user, database role, or application role. Objects created within a schema are owned by the owner of the schema, and have a NULL principal_id in sys.objects. Ownership of schema-contained objects can be transferred to any database-level principal, but the schema owner always retains CONTROL permission on objects within the schema.

Warning

In SQL Server 2005 the behavior of schemas differs from that in earlier versions of SQL Server. Code that assumes that schemas are equivalent to database users may not return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements has ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In a database in which any of these statements has ever been used, you must use the new catalog views. The new catalog views take into account the separation of principals and schemas that is introduced in SQL Server 2005. For more information about catalog views, see Catalog Views (Transact-SQL).

Deprecation Notice

CREATE SCHEMA statements that do not specify a schema name are currently supported for backward compatibility. Such statements do not actually create a schema inside the database, but they do create tables and views, and grant permissions. Principals do not need CREATE SCHEMA permission to execute this earlier form of CREATE SCHEMA, because no schema is being created. This functionality will be removed from a future release of SQL Server.

Permissions

Requires CREATE SCHEMA permission on the database.

To create an object specified within the CREATE SCHEMA statement, the user must have the corresponding CREATE permission.

To specify another user as the owner of the schema being created, the caller must have IMPERSONATE permission on that user. If a database role is specified as the owner, the caller must have one of the following: membership in the role or ALTER permission on the role.

Note

For the backward-compatible syntax, no permissions to CREATE SCHEMA are checked because no schema is being created.

Examples

The following example creates schema Sprockets owned by Annik that contains table NineProngs. The statement grants SELECT to Mandar and denies SELECT to Prasanna. Note that Sprockets and NineProngs are created in a single statement.

USE AdventureWorks;
CREATE SCHEMA Sprockets AUTHORIZATION Annik
    CREATE TABLE NineProngs (source int, cost int, partnumber int)
    GRANT SELECT TO Mandar
    DENY SELECT TO Prasanna;
GO

See Also

Reference

ALTER SCHEMA (Transact-SQL)
DROP SCHEMA (Transact-SQL)
GRANT (Transact-SQL)
DENY (Transact-SQL)
REVOKE (Transact-SQL)
CREATE VIEW (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.schemas (Transact-SQL)

Other Resources

User-Schema Separation

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Corrected syntax.