I've granted membership in the
db_datawriter roles for some testing being performed by a specific user.
Looking at the names of each role, I know what the roles do. However, I want to see where the permissions were assigned just to better understand.
To my surprise I see a blank slate for the
db_datareader schema for that database. I go onto read Understanding SQL Server fixed database roles and from there I know why I cannot see anything in that properties dialog:
The catch with this role is that the permission is implicit. That means if you query sys.database_permissions, you will not see any permission granted, either to the db_datareader role or directly to the user. Therefore, if you need to audit for everyone who has SELECT access to particular tables in a database, you'll have to query the membership of this group via the use of sp_helprolemember
I was aware of querying the roles which obviously works fine. So I thought this was perhaps a limitation of the GUI. I liked the permissions script from this answer and gave that a run. However, upon execution, that returns
permissionstate which is consistent with the above quote.
All that said, are these pre-canned roles infallible? Can I assume that no one can mess around with the implicit nature of their respective permissions i.e you can't remove
select rights from
db_datareader behind the scenes?