SQL - Schema

A Schema is a collection of database objects which includes logical structures such as views, stored procedures, databases, triggers etc.. The most usable advantage is that the schemas can be used to simplify managing permissions on tables and other objects. To understand schemas in a glance such as a container of various type of objects and we can assign user login permissions to a single schema so that the user can only access the objects they are authorized to access.

A database can consist of hundreds of schemas and each schema can have hundreds of tables, stored procedures, databases, triggers.

The schema has an owner, but the owner is not tied to the name. So, if an account owns a schema and the account must be removed from the database, the owner of the schema can be changed without breaking any code. Users in the database will be able to access any object owned by dbo without specifying the owner as long as the user has appropriate permission.

A database can have any number of Schema's. Schema objects can be created and manipulated with SQL. One table from a database can appear in two different schemas with the same name. A user can view any schema for which they have been assigned select privilege only. .

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 – Followings are the important components to define an schema into database- 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. DENY and REVOKE is supported inside CREATE SCHEMA statements. DENY and REVOKE clauses will be executed in the order in which they appear in the CREATE SCHEMA statement.

Important Points

Before using the schema against any other object -

1.  we should create them with GRANT, REVOKE, or DENY permissions on any securable in a single statement. Objects created by the CREATE SCHEMA statement are created inside the schema that is being created.
2. CREATE SCHEMA transactions are atomic. If any error occurs during the execution of a CREATE SCHEMA statement, none of the specified securable are created and no permissions are granted.
3. Securable 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.

How to create schema?

By using the CREATE SCHEMA clause, you can create any schema as given below:

---- Create Schema with AUTHORIZATION CREATE SCHEMA SALES AUTHORIZATION Ryan  ------- Create table within the schema boundry                 CREATE TABLE TB_SALES (Ord_No int, QTY int, COST FLOAT)  ------- Grant Select Access permission     GRANT SELECT ON SCHEMA::SALES TO Arjun  ------- Deny Select Access permission     DENY SELECT ON SCHEMA::SALES TO Uday; GO

Grant Execution Permission

To grant execute permission to all stored procedures within a schema, follow these steps:

Using SQL Server Management Studio, expand Security then Schemas under the database.

Right-click on the schema name and choose Properties.

Select the permissions page and click Add to choose database users or roles.

Once the users or roles are selected, a list of permissions will fill the bottom box.

To grant execute permission to all stored procedures; check Grant next to the Execute item.

How to View Schemas

To view all the schemas against any database, we can use the following script as given below:

USE Demo GO SELECT * FROM SYS.SCHEMAS GO

Conclusion

Schema is used to define your data components to identify the business requirements. The most useful feature to provide the security layer on the data objects because it can be used as the object container in the database and we can set the Grant permission for the specific users to pull the data only from their business circle.