I am a fresh admin in SQL DB and I need to learn how to establish the security settings in SQL DB. I have encountered an issue that is somehow unclear or even weird for me.

I know that if one user has Fixed Database-Level Roles db_datareader, he or she can perform "SELECT" on all user tables in DB. https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

But in my SQL DB, even the user which has already been added to that role can not perform "SELECT" on any table/view unless the "SELECT" permission is explicitly granted in "Schemas settings" which implies db_datareader role does not work at all.

Could anyone please give me advice on this? Many thanks

  • 2
    Either deny is active or you could have synonym in place. If you could elaborate the issue with example, one could help. Jan 11 at 5:47
  • 1
    @Learning_DBAdmin It cannot be DENY because he says "unless the "SELECT" permission is explicitly granted", it there was DENY in place it could not be bypassed by any SELECT granted
    – sepupic
    Jan 11 at 13:26
  • 1
    @Hongnam What do you mean by saying "schemas settings"?
    – sepupic
    Jan 11 at 13:28

these are the standard permission:

enter image description here

for this mapped user settings:

enter image description here

you have in place some kind of deny for your users.

  • One more time: if there was "some kind of deny", it could not be bypassed by granting explicit SELECT, DENY will always win.
    – sepupic
    Jan 14 at 13:51
  • Right! Deny wins above grant
    – MBuschi
    Jan 14 at 15:00
  • So if he says " unless the "SELECT" permission is explicitly granted" there is no DENY...
    – sepupic
    Jan 14 at 15:03

The answer to your question is in the result of following code:

execute as user = 'your_user'

select *
from sys.user_token
where principal_id <> USER_ID()


Please update your question with the result where 'your_user' is problematic user

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.