Schema concept

Schema is a new concept introduced into SQL Server from version 2005, it is a namespace used to group together tables that share a certain feature for easy management. If you don't use schema in the database, the default schema will be dbo.

For example in your database schema there are two main types of tables as follows:

News tables -> I will create a schema named news that includes tables related to news
Product tables -> I will create a schema named products including tables related to products
In a database, the schema name is unique, conforming to the syntax: server.database.schema.object

Advantages of schema:

When you group the tables according to the prefix defined by the schema, it is very easy to manage, identify, or decentralize the management of each schema for different users, this is the main role of the schema.

Create Schema (Create Schema)

Create Schema with SSMS:

Create Schema with T-SQL

Docs: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-schema?view=sql-server-ver15

Syntax:

 CREATE SCHEMA (<schema_name>)

For example:

 CREATE SCHEMA (<news>)

Drop schema (Drop schema)

Syntax

 DROP SCHEMA (<schema_name>)

For example:

 DROP SCHEMA news

Grant permission Schema

Assuming I want user toanngo92 to have management rights for schema news, just use the GRANT command, and delete the permission, use the DENY command.

Authorization:

 GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: news TO toanngo92

Remove permissions:

 DENY SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: news TO toanngo92

So grouping like this helps your database schema look more professional, easier to decentralize and more secure.