Database Roles dbo_owner and db_datareader

Question

Database Roles dbo_owner and db_datareader

Steve Vassallo

SSCommitted

Points: 1635
More actions
June 5, 2017 at 12:05 pm

#326406

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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 SSCommitted Points: 1635 More actions · Answer 2

But just to be sure, if you have multiple roles checked, db_owner still trumps them all ?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

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 SSC-Insane Points: 24038 More actions · Answer 4

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.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Database Roles dbo_owner and db_datareader

Cookies on SQLServerCentral