Why does SQL Server create schemas for the original database roles?

Question

Why does SQL Server create schemas for initial database roles such as db_accessadmin, db_datareader, etc.? I understand the roles and why they exist, but I do not understand why they are given schemas when creating the database. Does anyone use them? I cannot imagine that Microsoft recommends adding tables to them. I do not see the addition of an employee or product table in db_datawriter. Are there any hidden system objects that belong to them?

Answer 1

Others have stated compatibility with previous / reverse, without explaining this.

When entering SQL Server 2005, they introduced user / schema separation . Until that time, each user and role implicitly had a schema associated with it with the same name (and there was no way to create schemas other than by creating users or roles).

So, for those created in roles that existed in the 2000 (or earlier) versions of SQL Server, there was always a schema "available" in the database with the same name as this role. Thus, some code may have been written, suggesting that such schemes exist; later versions of SQL Server ensure that this is still the case so as not to violate such code.

Answer 2

Inheritance.

You should use an explicit GRANT (say GRANT ALTER USER TO ...), and not use legacy fixed database roles.

If you use sp_grantdbaccess , you also get the created schema: you must use CREATE USER