What are Schemas?

They are collections of objects within a given database, which organize various aspects and are important for security segmentation, making it easier to manage objects and data.

As of SQL Server 2005, any and all database objects no longer belong to a user to belong to a Schema. Therefore, Schema is the bridge of association between the user (or group of users) and one (or more) of the bank object (s).

What are the advantages of using it?

The first advantage is the permission of users and groups. When a user is authorized in a Schema, it can be authorized in several databases at the same time whose objects are included within a Schema.

The second advantage is the physical grouping of data. Some servers, such as the latest versions of SQL Server, allow objects from the same Schema, even if they are split into multiple databases, to be physically grouped for backup and load administration.

In what situations is your use recommended?

In databases with multiple databases (which have multiple systems, for example) and need to authorize or revoke users and groups quickly.

In databases whose permission is essential for data security (old client-server systems, for example, that rely heavily on the database for implementing business rules).

In segmentation of data by user group. You can have two tables with the same name in the same database, each one being in a different Schema.

What are Schemas?

It is a container that can hold multiple objects. They are used to manage and organize the objects in the database. You can logically separate procedures , views , triggers, sequences and so on. The objects belong to the schema, so the permissions are applied to the schemas, so you can give permissions to users so they only access the objects that are allowed in a more organized way.

Organization

User X has access to all objects related to the company, in this case you would have to apply permissions to all the objects that he has access to separately, for example:

In the drawing below, user X would have access to the employee and department tables:

Usingschematoorganize,youwouldgivepermissiontouserXintheenterpriseschema,eg:

Schemas provide the opportunity to simplify security administration, backup / restore, and database management, allowing database objects, or entities, to be logically grouped together.

Source: SQL Server Best Practices - Implementation of Database Object Schemas