How can I restrict access to HR data in SQL Server?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply