June 5, 2017 at 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
June 5, 2017 at 12:36 pm
Steve Vassallo - Monday, June 5, 2017 12:05 PMHello 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 restrictiveThank 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
June 5, 2017 at 12:40 pm
But just to be sure, if you have multiple roles checked, db_owner still trumps them all ?
June 5, 2017 at 12:47 pm
Steve Vassallo - Monday, June 5, 2017 12:40 PMBut 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
June 5, 2017 at 3:03 pm
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]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply