With SQL server, do default roles 'trump' defined securables and/or custom roles?
I am cleaning up security on my servers, and I have accounts that have datareader membership to the database, membership to a custom security that grant select permissions to tables, and then there are tables from the database that are called out under Securables with select privileges to some tables.
My experience would tell me that db_datareader to the database would do the same thing. Am I correct?
With SQL server, do default roles 'trump' defined securables and/or custom roles?
Not necessarily, permissions are cumulative in SQL Server. SQL Server follows a denied-by-default methodology where permissions need to be given either by an explicit GRANT or by being added to a role that already has been granted such permissions such as the default db_datareader role. Explicit DENY always overrules any granted permissions (except the SysAdmin server role).
I have accounts that have datareader membership to the database, membership to a custom security that grant select permissions to tables...My experience would tell me that db_datareader to the database would do the same thing. Am I correct?
Yes, db_datareader will grant access to SELECT from all Tables (and selectable objects, e.g. Views) from within that database. Anyone in that role and with an explicit SELECT granted to a particular object has redundant privileges provisioned. (It won't harm anything, but is not necessary.)
Conversely, obviously anyone who has only a few explicit Tables granted with SELECT won't have access to SELECT from any other objects. If they are added to the db_datareader role, then they would have access to SELECT from all objects (potentially more than they had before) within that database.
