0

Is it possible in SQL Server 2017 and upwards to audit the access to certain rows in a table? Imagine a customer table, and we wanted to track when users selected the data for premium customers identified by their primary key; or maybe employees querying the salary information of their fellow employees (but not too worried about them querying their own salary information).

With the wealth of auditing and security technologies that Microsoft are throwing at SQL Server these days, is there anything among e.g. extended events, row level security, auditing etc that would help me?

1 Answer 1

0

Wouldn't it make more sense to set the permissions so they can only access the things they are authorised to rather than audit and log that they did something unauthorised - that said:-

I think if you built 2 views which filtered the base tables to "sensitive" and "normal" rows and only permitted access via the views you could then audit the views.

3
  • The scenario I am thinking of is a multi-tenanted SaaS platform that my employers created, that they use for their own data as well as that of their customers. In the normal course of operations, my coworkers would need access to our customers' data for support, but shouldn't have access to our own data in the same tables. Commented Apr 30, 2020 at 2:24
  • wouldn't using views solve that for you ? Commented Apr 30, 2020 at 7:26
  • People would still have access to the underlying tables via SSMS; and they might have genuine reasons for accessing a row, so we don't want to block them outright, just record the fact that they have somewhere. Commented May 6, 2020 at 1:40

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.