17
In which situations your use is recommended?
2 answers
14
What are Schemas?
They are collections of objects within a given database, which organize various aspects and are important for security segmentation, facilitating the administration of objects and data.
Since SQL Server 2005, any database object has ceased to belong to a user to belong to a Schema. Schema is thus the bridge of association between the user (or group of users) and one (or more) database object(s.
What are the advantages of using it?
The first advantage is the permissioning of users and groups. When a user is authorized in a Schema, it can be authorized in multiple databases at the same time whose objects are included within a Schema.
The second advantage is the physical grouping of the data. Some servers, such as the latest versions of SQL Server, allow objects from the same Schema, even if they are divided into multiple databases, to be physically grouped together for backup administration and loading.
In which situations your use is recommended?
In databases with multiple databases (which have multiple systems, for example) and it is necessary to authorize or revoke users and groups quickly.
In databases whose permissioning is an essential feature for data security (old client-server systems, for example, that rely heavily on the database for implementing business rules).
In data segmentation by user group. You can have in the same database two tables with the same name, each in a different Schema.
11
What are Schemas?
It is a container that can contain multiple objects. They are used to manage and organize the database objects. You can logically separate procedures, views, triggers, sequences etc. Objects belong to the schema, so permissions are applied to schemas, so you can give permissions to users to access only the objects you have permission to access in a more organized way.
Organizing
User X has access to all company related objects, in this case you would have to apply permissions to all objects he has access to separately, example:
In the drawing below, user X would have access to employee and department tables:
Using schema to organize, you would give permission to user X in the enterprise schema, example:
The schematics 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
Browser other questions tagged sql sql-server-2012
You are not signed in. Login or sign up in order to post.
I can’t think of a situation where they’re not used, but still, the question is good. + 1.
– Oralista de Sistemas