CREATE SCHEMA statement in Transact SQL
CREATE SCHEMA is the Transact SQL statement to create a database schema in SQL Server. Regarding databases, a schema is a structure that allows grouping and organizing the different lower-hierarchy objects such as tables, views, stored procedures, functions, etc. in a specific logical set, so that the objects are grouped into independent namespaces.
It is important to attend to the hierarchical order of all participating components of a database: Server-Database-schema-DatabaseObject.
CREATE SCHEMA
When we create a database in SQL Server, it already contains a series of predefined schemas that correspond to the name of the system’s built-in users. By default, when we create a database object and don’t specify the schema, it will automaically be created in the schema dbo.
If we have authorization and permissions from the database administrator, we can create new schemas and move existing objects to it with the SQL ALTER statement.
We will be able to view the default system schemas by displaying the following directory path of our database: Databases-database-Security-Schemas as we can see in the following image.
These system schemas cannot be deleted.
Database schemas are also a very useful resource in managing permissions and security of a database. As database administrators, we will be able to associate a schema to a user and manage the specific permissions that are applied to the objects of that schema for that user.
Next, we will see how to create a new schema by direct Transact SQL statement, not forgetting that the SQL Server Management Studio (SSMS) graphical editor can also be used if desired.
Once in our SQL Server Management Studio (SSMS) editor window, we can create a new schema with the statement CREATE SCHEMA schemaname; We can add the AUTHORIZATION clause to the statement to define a user as the owner of a particular schema. The sentence would then be as follows:
CREATE SCHEMA schemaname AUTHORIZATION userName;
The username that accompanies the AUTHORIZATION clause must already exist in the database. If we don’t use the AUTHORIZATION clause, the schema will default to the user we are using.
SCHEMA PERMISSIONS
Optionally, as an administrator, we can manage user permissions and roles with respect to the schema. This way, we can limit the action of the different users and roles with respect to the objects contained in the particular schema.
These settings can be made before finishing creating the schema from the editor or once it has been generated. In that case, simply update the schemas with F5, move to the newly created one and select the properties option with the mouse right click.
These permissions can also be managed with SQL (GRANT , REVOKE) statements, which we will see in the sections referring to DCL (Data Control Language).
We will now explain and illustrate how to manage the permissions referring to the schema from the graphical editor of SQL Server Management Studio (SSMS).
- Once the pop-up window is open, we select permissions.
- Click Search and a new pop-up window will open where we can select the users or roles to apply permissions to.
- From the object types option we can filter by type of object (user, database role or application role).
- From the browse window, select the users/roles from the previous filter to which the permissions will apply.
- Once the desired users and roles have been selected, we can easily manage permissions by selecting those we want to grant and those we want to deny.
In this example, we are granting query permissions to the user guest on the objects belonging to the schema testschemaeditor and at the same time we are preventing him from performing operations that alter the contents of the database in any way, whether they are inserts, deletes or updates.
