May 9, 2007 at 11:38 am
Hi,
I wanted to know what are the net effective permissions applied to a user if both the database roles "db_datareader" and "db_denydatareader" are selected for a user. I could understand from BOL that these two are complimentary roles to each other but wondering why sql server let us select both for a given user.
Thanks,
Phani
May 9, 2007 at 12:13 pm
Phani,
the effective permissions would be reading on all tables and views will be denied. There's one exception, that is if the user is also a member of the db_owner group or sysadmin. In that case the deny won't have any effect.
Markus
[font="Verdana"]Markus Bohse[/font]
May 9, 2007 at 1:18 pm
It is incorrect, at least in SQL Server 2000. In SQL Server, DENY has higher priority over other roles. DENY overwrites other roles even for db_owner. But DENY would not overwrite sa.
I tested an account with both db_datareader and db_denydatareader roles. Then, open QA using this account. This account does not have any permission to read any data from both tables and view.
Further more, I grant this account with db_owner, and continue to test. I have other permission except datareader.
May 9, 2007 at 1:21 pm
To answer the question of 'why' it appears this was a design flaw IMO...
Ben Sullins
bensullins.com
Beer is my primary key...
May 9, 2007 at 1:31 pm
No the idea is to apply most restirctive. It is not a design flaw as you can have a user who is a member of 2 different Roles in the system and each role could have opposing permissions or even the user has deny but added to a group thathas grant. It is up to you to setup and maintain the permissions so you get the effect you want.
May 10, 2007 at 12:57 am
SQL Oracle is right and I was a not clear enough.
You can deny reads or writes to memebrs of the db_owner role, but you can't do it for the actual database owner (dbo).
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply