Timeline for How can I restrict access to HR data in SQL Server?
Current License: CC BY-SA 4.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 17, 2022 at 1:34 | comment | added | Tarzan | Another option to consider is column masking. | |
| Jul 17, 2022 at 1:33 | vote | accept | Tarzan | ||
| Jul 9, 2022 at 14:01 | answer | added | Aleksey Vitsko | timeline score: 5 | |
| Jul 9, 2022 at 10:23 | comment | added | Dan Guzman |
@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.
|
|
| Jul 9, 2022 at 10:09 | history | edited | Dan Guzman | CC BY-SA 4.0 |
Added relevant information from comments.
|
| Jul 9, 2022 at 7:59 | comment | added | Tibor Karaszi | 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. | |
| Jul 8, 2022 at 22:59 | comment | added | Tarzan | 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. | |
| Jul 8, 2022 at 22:04 | history | asked | Tarzan | CC BY-SA 4.0 |