2

We have a SQL Server database with an HR schema that contains human resources data. It includes salary and other private information. Database owners, sysadmins, and members of an [Executives] role are allowed to see the HR schema.

Some of the other users are members of the db_datareader role which has the ability to read the data from any table of the database. They were added to the role before the HR security requirement existed.

What is a strategy to deny access to all other users?

Improve this question
4
  • Some of the other users are members of the db_datareader role which has the ability to read the data from any table of the database. They were added to the role before the HR security requirement existed.
    – Tarzan
    Commented Jul 8, 2022 at 22:59
  • You can DENY on the HR schema to those users directly. Or create a role and add users to that role and deny for the role.
    – Tibor Karaszi
    Commented Jul 9, 2022 at 7:59
  • 1
    @Tarzan, I edited your question to include the relevant info from your comment. Personally, I avoid using built-in database roles unless the db is dedicated to a specific application or group of users. The DENY approach Tibor suggested is probably the easiest way to accomplish this. Another method is a custom role like limited_db_datareader granted SELECT on all schemas except HR.
    – Dan Guzman
    Commented Jul 9, 2022 at 10:23
  • Another option to consider is column masking.
    – Tarzan
    Commented Jul 17, 2022 at 1:34

1 Answer 1

Reset to default
5

First thing you have to do is to remove other users from db_datareader role

alter role db_datareader drop member user1
alter role db_datareader drop member user2

Then create custom database role, like other_users, you name it, add members:

alter role [other_users] add member user1
alter role [other_users] add member user2

then grant select on database to role

grant select to [other_users]

Then deny access to HR schema to other_users

deny select on schema::HR to [other_users]

This way you will have other users be able to read from any other schema or table, except HR schema

Improve this answer
6
  • 2
    Members of db_datareader will not be able to read if there is an active DENY.
    – David Browne - Microsoft
    Commented Jul 9, 2022 at 22:43
  • 1
    @DavidBrowne-Microsoft you are right, edited my answer and removed that false statement. Maybe it was true for older SQL Server versions ? Anyway, removed that part. Thanks for pointing that out!
    – Aleksey Vitsko
    Commented Jul 10, 2022 at 6:54
  • 1
    @AlekseyVitsko, I believe DENY has always taken precedence over GRANT, at least going back to SQL Server 4.21a ca 1993.
    – Dan Guzman
    Commented Jul 10, 2022 at 11:59
  • 2
    From memory, DENY was introduced in 7.0 ("the new architecture"), and it has been working the same way since. Prior to 7.0, we only had GRANT and REVOKE and end result was dependent on what order you executed the those statements in (nightmare). Again, this is from memory, and it has been a while since those days. :-)
    – Tibor Karaszi
    Commented Jul 10, 2022 at 13:35
  • @DanGuzman DENY > GRANT always, that is correct - but something made me think db_datareader > DENY. I believe one of questions in certification exam was stating that and I was under impression that I even tested that out back then. Anyway, as we see that is not true :) so I removed that line from the answer
    – Aleksey Vitsko
    Commented Jul 10, 2022 at 16:44

Your Answer

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

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