Create a Database Schema
This topic describes how to create a schema in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.
In This Topic
-
Before you begin:
-
To create a schema, using:
Limitations and Restrictions
-
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.
-
When creating a database object, if you specify a valid domain principal (user or group) as the object owner, the domain principal will be added to the database as a schema. The new schema will be owned by that domain principal.
Security
Permissions
-
Requires CREATE SCHEMA permission on the database.
-
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.
To create a schema
-
In Object Explorer, expand the Databases folder.
-
Expand the database in which to create the new database schema.
-
Right-click the Security folder, point to New, and select Schema.
-
In the Schema - New dialog box, on the General page, enter a name for the new schema in the Schema name box.
-
In the Schema owner box, enter the name of a database user or role to own the schema. Alternately, click Search to open the Search Roles and Users dialog box.
-
Click OK.
Additional Options
The Schema– New dialog box also offers options on two additional pages: Permissions and Extended Properties.
-
The Permissions page lists all possible securables and the permissions on those securables that can be granted to the login.
-
The Extended properties page allows you to add custom properties to database users.
To create a schema
-
In Object Explorer, connect to an instance of Database Engine.
-
On the Standard bar, click New Query.
-
Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2012; GO -- Creates the schema Sprockets owned by Annik that contains table NineProngs. -- The statement grants SELECT to Mandar and denies SELECT to Prasanna. CREATE SCHEMA Sprockets AUTHORIZATION Annik CREATE TABLE NineProngs (source int, cost int, partnumber int) GRANT SELECT ON SCHEMA::Sprockets TO Mandar DENY SELECT ON SCHEMA::Sprockets TO Prasanna; GO
For more information, see CREATE SCHEMA (Transact-SQL).
