My use case is as follows:
A table in my database is accessible by application A, B, C, etc each of whom has their own database principal. We want to start limiting the principals that can access certain table in my database, i.e. we want it such that only application A has access to said table. The idea is to relegate access to the table such that B and C calls application A instead of going to the table directly.
One naive way is to remove the principal B and C's access to the table straight away, but this will cause an application outage on B and C. A better approach is to reimplement B and C to call A first before taking away their access.
The challenge is that B and C might be accessing the table via an opaque library. We have been slowly phasing out the opaque library in favor of calling application A, but we are not 100% sure that B and C no longer accesses the table.
I have been thinking that I could check the database audit log to check which principals have been accessing that table. I have been using Azure SQL Auditing feature in my project which gives me the stats on individual statement, including its query string.
However, the query string itself might be composed of JOINs of several tables, or the string is an EXEC of a stored procedure, i.e. the granularity of the audit is on the statement level. It doesn't tell me explicitly which tables are involved in a given statement.
Is there a way to effectively capture this information that table T has been used by principals A, B, C in the past X hours?