Schema and Security Control in SQL server

dbo is always seen as a SQL server table's schema. dbo stands for database owner. The dbo schema is the default schema of every database. When user created with CREATE USER T-SQL command have dbo as their default schema. The dbo schema is owned by the dbo user account. 

SQL server also provides other pre-defined schemas that have the same names as the built-in db users and roles: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, db_securityadmin. These exist for backward compatibility. The recommendation is to not use them for user objects. 

User can drop the schemas that have the same names as the fixed database roles - unless they're already in use, in which case the drop-command will simply return an error and block the drop of the used schema.

For example:

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin')

DROP SCHEMA [db_accessadmin]

GO

If user drops these schemas from the model database, they won't appear in new databases. Schemas that contain objects cannot be dropped.

The following schemas cannot be dropped:

• dbo
• guest
• sys
• INFORMATION_SCHEMA

Below is the screenshot of the database AdventureWorks2019, under the Security/Schemas folder, we can see those pre-defined schemas. Those schemas can be dropped are in the blue rectangle, and the schemas can't be dropped are in the red rectangles.

Figure 1.

Schemas allow the user to group the related tables together to manage and assign permissions to tables as a group. These groups are usually mapping the structure of the company. Each group creates their own data, but it still contributes to the data pool of the company. 

We can see from the Figure 1, there are user defined schemas: HumanResource, Person, Production, Purchasing Sales. Under each schema, there are many data tables:

Figure 2.

Now let me use another DB Red30Tech as an example for the access control of the databases and the schemas. I have an user account as test1.

Figure 3.

The account test1 can access KinetEcoDW, Red30Tech and WideWorldimporters databases. 

Figure 4.

But test1 isn't the owner of any schema.

Figure 5.

Test1 has been assigned as the 'db_datareader' which means Test1 can only read the allowed databases.

Figure 6.

For the security level of test1, it could only insert record to the tables with schema 'sales', but test1 has no other permission to the tables with schema 'sales'. 

Figure 7.

Now let's test the settings.

Sign in the DB as user test1.

Figure 8.

Try to access the database AdventureWorks2019, SQL server gives the error message since it hasn't been assigned permission to access this database as it's configured in Figure 4.

Figure 9.

Now test1 tries to insert a new record to the table dbo.Customers. And the system returns the error message since it only has the 'read' permission  to the 'dbo' schema as it's configured in Figure 6.

                                                                                    Figure 10.

Then Test1 tries to insert a new record to the table 'sales.Customers', and it's successful this time which proves the the Figure 7 that Test1 has been granted the 'insert' permission to the table with the 'sales' schema.

                                                                                     Figure 11a.

Search the Customers table with LastName starts with 'Sakura', it returns the record that I just inserted.

                                                                                   Figure 11b.

Also, test1 failed in trying to modify the sales.Customers table because it has no other permission to the table.

                                                                                      Figure 12.

The database - level roles and permission table. 

                                                                                    Figure 13.

                          

Reference:

https://learn.microsoft.com/en-us/sql/relational-databases

https://www.linkedin.com/learning/microsoft-sql-server-2022-essential-training/