title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | monikerRange | |||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Create a Database Schema |
Learn how to create a schema in SQL Server by using SQL Server Management Studio or Transact-SQL, including limitations and restrictions. |
VanMSFT |
vanto |
07/31/2023 |
sql |
security |
conceptual |
|
|
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server] This article describes how to create a schema in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].
-
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.
-
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.
-
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, select Search to open the Search Roles and Users dialog box.
-
Select OK.
Note
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.
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.
-
In Object Explorer, connect to an instance of [!INCLUDEssDE].
-
On the Standard bar, select New Query.
-
The following example creates a schema named
Chains
, and then creates a table namedSizes
.CREATE SCHEMA Chains; GO CREATE TABLE Chains.Sizes (ChainID int, width dec(10,2));
-
Additional options can be performed in a single statement. The following example creates the schema
Sprockets
owned byJoe
that contains the tableNineProngs
. The statement grantsSELECT
toBob
and deniesSELECT
toJohn
.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
-
Execute the following statement to view the schemas in the current database:
SELECT * FROM sys.schemas;
For more information, see CREATE SCHEMA (Transact-SQL).