SQL Server – Why Roles and Schemas Have Mostly the Same Name

rolesql serverusers

When I create a database and add a user I get an interface with options. On that interface I can choose:

  1. Owned Schemas
  2. Membership

This question on dba.stackexchange HERE says schemas and roles are not related. This has confused me in the past since they have the same names. Why is this?

Also. In the Schemas folder, I see dbo, INFORMATION_SCHEMA, and sys. But in the user dialogue, under 'Owned Schemas' I don't see these options. Why is this?

Best Answer

This has confused me in the past since they have the same names. Why is this?

I think this is simply becuase of convention, it is easier to identify the owner of the schema.

Defualt schemas dbo, INFORMATION_SCHEMA, sys schemas are belongs to dbo, INFORMATION_SCHEMA, sys users respectively and these are inherited from model database.

INFORMATION_SCHEMA and sys are used to store system objects so that we can't select to store user-defined objects. dbo is the default schema so we don't need to select it.

Documentation

Related Question