SQL server database Role management - Azure SQL MI

Question

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?

Answer 1

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;

• Venkat: So basically in this case we have to create two seperate roles and add the users in the respective roles. Is giving permissions to users directly adivisable?
• David Browne - Microsoft: Granting permissions directly to users is not typically the best choice, as users come and go and multiple users often have the same needs.
• Venkat: I will assume, We can create multiple(n number of) roles based on the requirements and add users, and not grant permissions directly to users. Thanks a lot for your response David.