Create a database schema

This article describes how to create a schema in SQL Server by using SQL Server Management Studio or Transact-SQL.

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 is added to the database as a schema. The new schema is owned by that domain principal.

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 meet one of the following criteria: membership in the role or ALTER permission on the role.

Using SQL Server Management Studio to create a schema

1. In Object Explorer, expand the Databases folder.
2. Expand the database in which to create the new database schema.
3. Right-click the Security folder, point to New, and select Schema.
4. In the Schema - New dialog box, on the General page, enter a name for the new schema in the Schema name box.
5. In the Schema owner box, enter the name of a database user or role to own the schema. Alternately, select Search to open the Search Roles and Users dialog box.
6. Select OK.

A dialog box will not appear if you are creating a Schema using SSMS against an Azure SQL Database or an Azure Synapse Analytics. You will need to run the Create Schema Template T-SQL Statement that is generated.

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.

Using Transact-SQL to create a schema

1. In Object Explorer, connect to an instance of Database Engine.
2. On the Standard bar, select New Query.
3. The following example creates a schema named Chains , and then creates a table named Sizes .

CREATE SCHEMA Chains; GO CREATE TABLE Chains.Sizes (ChainID int, width dec(10,2)); 

CREATE SCHEMA Sprockets AUTHORIZATION Joe CREATE TABLE NineProngs (source int, cost int, partnumber int) GRANT SELECT ON SCHEMA::Sprockets TO Bob DENY SELECT ON SCHEMA::Sprockets TO John; GO 

SELECT * FROM sys.schemas;