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

Answer 1

these are the standard permission:

for this mapped user settings:

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

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