Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Schemas and Roles Expand / Collapse
Posted Sunday, April 21, 2013 12:23 PM


Group: General Forum Members
Last Login: Friday, October 31, 2014 2:43 PM
Points: 10, Visits: 122

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.

Post #1444811
Posted Monday, April 22, 2013 12:06 AM



Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 7,978, Visits: 16,009
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"
Post #1444863
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse