1

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?

1 Answer 1

3

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.

1
  • @PaulMcKibben Sure thing, no prob! Best of luck!
    – J.D.
    Aug 30, 2022 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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