Can I trust the effective permissions of default database roles?

Question

I've granted membership in the db_datareader and 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 NULL for permissiontype and 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?

Answer 1

Here's what I just tried in a test database (SQL Server 2012):

GRANT EXEC ON SCHEMA::dbo TO db_datareader

...And the response I got:

Msg 4617, Level 16, State 1, Line 1
Cannot grant, deny or revoke permissions to or from special roles.

So I'm assuming you're reasonably safe. Of course, that's not to say you couldn't do something wacky like having a stored procedure testing for membership in those roles, and implementing its own security functionality based on the results.

Answer 2

Your question:

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?

According to the documentation on MSDN, fixed database roles, including db_datareader and db_datawriter cannot be modified:

These roles exist in all databases. The permissions assigned to the fixed-database roles cannot be changed.

Therefore, you can rest-assured assignment of rights via these roles are fixed and cannot be modified.

Granting membership in db_datareader is the equivalent of doing GRANT SELECT ON DATABASE::[db-name] TO [principal];