26

Are there differences between Owned Schemas and Role Members?

From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked.

However, the Owned Schemas is not compulsory. It really make me wonder why there is a need to have the Owned Schemas in there.

enter image description here

Leigh Riffel
  • 23,534
  • 16
  • 75
  • 147
Jack
  • 2,331
  • 14
  • 32
  • 42

2 Answers2

18

Apples and Oranges. Roles are roles and schemas are schemas. The fact that there is a role called db_accessadmin and also a schema called db_accessadmin does not mean a role is a schema nor that a schema is a role. Roles are security membership containers, a principal is member of a role. Schemas contain database schema bound objects and are owned by a principal. When you create a new user you can choose his default schema, add him to certain roles, and grant him ownership of schemas. Although the actions are separate, the designers of this dialog feel they are somehow related as to merit all that UX real estate (that is questionable, but a completely different topic).

Remus Rusanu
  • 50,693
  • 3
  • 90
  • 168
  • [Reading between the lines](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server), I'm guessing the point of having these schemas is so that e.g, you can have certain objects be writable by users with the `db_datareader` role *and* schema, which are not necessarily readable by anyone else. Would work well for an app which only needs read access to `dbo`, but needs to install some stored procs or triggers. Of course, that still requires you to add each user to both the role and the schema manually. – jpaugh Nov 08 '18 at 16:28
1

Well.. this is a very old thread but still 2 cents from me for those who will still visit here. This link has the best answer for the above query.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/ownership-and-user-schema-separation-in-sql-server

An excerpt from the same:

SQL Server ships with ten pre-defined schemas that have the same names as the built-in database users and roles. These exist mainly for backward compatibility. You can drop the schemas that have the same names as the fixed database roles if you do not need them.

John K. N.
  • 14,660
  • 9
  • 40
  • 93
Ganesh
  • 21
  • 2