SQL server database Role management - Azure SQL MI In my database I have multiple users with SQL server authentication login and AD login.

For ex: User 1 and user 2 are from same team(ABC), if I create a role db_abc

User 1 needs read access to table a and table b.
User 2 needs read access to table a, b and additionally to table c. 

How do I manage this case? Should permissions be given at user level than to roles.? 
What is the best practice in managing users and roles?
What is the best practice in managing users and roles?


Create custom roles and grant permissions to the roles; then add users to the roles.

eg:

create role reader_abc
grant select on dbo.a to reader_abc
grant select on dbo.b to reader_abc
grant select on dbo.c to reader_abc

create role reader_ab
grant select on dbo.a to reader_abc
grant select on dbo.b to reader_abc

alter role reader_abc add member alice
alter role reader_ab add member joe


You can also assign permissions to an entire schema:

grant select on schema::dbo to report_users;


Or the whole database: 

grant select to report_users;