How to check all access rights for specific user in Azure SQL Database?

Question

I have below questions about schema/privilege:

1. May User have multiple DB roles(schema)?
2. What is db_denydatareader used for? (it seems can have different DB roles among databases, right?)
3. What are difference between db_datareader and db_denydatawriter if only want user to read data in particular database?

4. I tried to revoke SELECT right from schema (TestUser is with default schema db_datareader), why can it still search for tables?

   REVOKE SELECT ON SCHEMA::db_datareader TO TestUser;
   

5. How can I grant select and update permissions to few tables only to user but not all tables? (i.e. no delete and insert permissions)

6. What are these system privileges referring to as I could not find in sys.objects table?

   select * from sys.database_permissions where major_id <= 0;
   

Thanks.

Answer 1

It seems you think schemas and roles are the same but they are not the same. Roles are security membership containers, a principal can be member of a role. Schemas contain database schema bound objects, they help to group database objects together, and are owned by a principal. When you create a new user you can choose his default schema, add him to certain roles, and grant him ownership of schemas.

Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

About the difference between db_datareader and db_denydatawriter. The db_datareader grants select permissions on all tables, and It does not affect any insert, update, delete permissions. Meanwhile db_denydatawriter denies insert, update and delete permissions on all tables, it denies permission to do any changes to any table. Even if someone was granted insert permissions directly they would still not be able to insert, because deny overrules grant. Assigning a user to the db_denydatawriter role means that they will never be able to make any changes to the database, regardless of what other permissions they have. Deny takes precedence over grant.

About question #4, you can group tables on schemas and then DENY SELECT permission over the schema to a principal or user. db_datareader is a fixed database role and it is not a schema.

DENY SELECT ON schema::[SchemaName] TO [user_name]

Similarly you can grant SELECT and UPDATE permissions over an schema on the database, that contains a group of tables.

GRANT SELECT, UPDATE on SCHEMA::SchemaName TO [user_name]

You can find the list of database roles here.