SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Schemas and Roles


Schemas and Roles

Author
Message
itlk
itlk
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 122
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.
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19738 Visits: 17239
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search