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