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


Database Roles dbo_owner and db_datareader


Database Roles dbo_owner and db_datareader

Author
Message
Steve Vassallo
Steve Vassallo
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1019 Visits: 249
Hello all.. its been awhile and I have been way from SQL for a couple years.. Getting back into it now. Quick question on permissions. If someone is granted db_owner and db_datareader on a database, is their effective permissions db_owner?
Just verifying that the permissions here are not setup as least restrictive

Thank you
Steve
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33158 Visits: 9414
Steve Vassallo - Monday, June 5, 2017 12:05 PM
Hello all.. its been awhile and I have been way from SQL for a couple years.. Getting back into it now. Quick question on permissions. If someone is granted db_owner and db_datareader on a database, is their effective permissions db_owner?
Just verifying that the permissions here are not setup as least restrictive

Thank you
Steve

Essentially yes. db_owner can pretty much do anything in the database - which would include select against all tables and views like db_datareader.

Sue




Steve Vassallo
Steve Vassallo
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1019 Visits: 249
But just to be sure, if you have multiple roles checked, db_owner still trumps them all ?
Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33158 Visits: 9414
Steve Vassallo - Monday, June 5, 2017 12:40 PM
But just to be sure, if you have multiple roles checked, db_owner still trumps them all ?

Yes but it's the permissions that matter, not necessarily a role in particular. In general, permissions are cumulative with deny taking precedence.
db_owner would be the role with the most privileges in terms of database roles. And as I said, they can pretty much do anything in a database.

Sue




Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13168 Visits: 879
Sue's answer is correct as long as you start do dabble with DENY or roles like db_denydatareader. In difference to sysadmin, you can deny db_owner rights. And DENY always takes precedence over GRANT.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
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