April 21, 2013 at 12:23 pm
Hi,
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:
Table: dbo.Test
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.
April 22, 2013 at 12:06 am
A schema is used to collectively identify set objects, the schema can also be used to control permission lists defining access to the collective objects. Users create objects under schemas typically for business separation.
The database role is more a general gathering of users which can be either granted permission on required objects or granted schema access allowing the schema to do the work.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply