1

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?

Improve this question

1 Answer 1

Reset to default
3

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;
Improve this answer
3
  • 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?
    – Venkat
    Dec 3, 2019 at 18:41
  • 1
    Granting permissions directly to users is not typically the best choice, as users come and go and multiple users often have the same needs.
    – David Browne - Microsoft
    Dec 3, 2019 at 18:52
  • 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.
    – Venkat
    Dec 4, 2019 at 2:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.