I am confusing about the usage of roles and schemas. I have done some research on Internet, and I found that also many others are confused, with some admins claiming, that they avoid using schemas to avoid complications.
I believe schemas can be useful and simplify things. The problem I see is the overlapping feature / functionality between roles and schemas.
For example, when I create a role rlTest, then in the SSMS in the properties of this role Properties\Securables I can specify the schema schTest and set the permissions for the role on this schema.
On the other hand, in the properties for the schema schTest I can set up permissions for the role rlTest: Properties\Permissions, field ‘Users and Roles’.
I think these two approaches are not equivalent and can lead to conflicts.
So what would be the proper using of schemas/roles? I have the following example:
Users: usr1 – read permissions
usr2 – read permissions, insert permissions
usr3 – full access
Roles: rl1, rl2 rl3
Schemas: dbo, sch1, sch2, sch3
Thank you for your insight.