2

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?

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];

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.