Schemas permission in SQL DB

Question

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://learn.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

• Learning_DBAdmin: Either deny is active or you could have synonym in place. If you could elaborate the issue with example, one could help.
• sepupic: @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: @Hongnam What do you mean by saying "schemas settings"?

Answer 1

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.

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

Answer 2

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()
revert

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