How to know database(table) changed with only dbreader role(read permission) ?

Question

My application is a sub-application which could know a database(table) changed by main application through SqlTableDependency
(https://github.com/christiandelbianco)

When I was using SqlTableDependency, I had sysadmin role which can create triger, contract, message, queue, service broker, procedure but now I only have dbreader role(read permisiion).

What is best solution for this limited situation?

Or what if the main application creates a .txt file in a folder whenever the database(table) is changed, how can I utilize the creation of a .txt file ? (as notification)

Thank you so much !

Answer 1

Hi,

How to know database(table) changed

I assume you mean data in the table and not changes in the table structure...

What is best solution for this limited situation?

If you have only read permission then you have two options: (1) Get help from someone who have more permission in order to built a solution - there are multiple option to track changing in table data. (2) You can pull the data from the table every X time and store it in a database which you do have control (can be simple excel file). Each time you pull the data compare it to the data you have before - this is basically an awful solution which mean that you manage another database instead of getting help from the person who have the permission to design a solution

For option 1 you can use multiple options like on of the following:

1. Use trigger on INSERT, UPDATE, DELETE
2. Use extended event to push information each time data changes
3. Use Change Data Capture
4. Use Change Tracking
5. Use Temporal tables
6. ...

In anyway, in order to design a solution in the SQL Server you will need to do something in the SQL Server which mean that read only will not be enough for the step of building the solution. This leave you with the option to manage external database (one type or another).

Answer 2

What is the best situation for your case is difficult to say since we only have a general description.

A Ronen alludes to, you will typically need help from the rest of the application. A common technique is to have a dateime2(3) column in the table that tracks when a row was last updated. You read all rows that have been changed since last time you read. A potential problem is that for UPDATE statements. the code must set this changedtime column explicitly, which an oblivious programmer can forget. A more reliable solution is to have a column of type rowversion; such a column is set automatically by SQL Server. None of these solution tracks deletes though.

There can also be issue if updates occur while you read what has changed recently. The higher the update frequency is, the more problematic this can be.

Ronen listed a few more methods, and which fits best depends on the situation. Change Tracking is more reliable than the solutions I outlined, but requires more setup.

Answer 3

Hi @Kay_Lee ,

For read only permission, the method that you can choose is too less, suggest you ask someone who has admin permission or in db_owner role to help you.

Please reading below blog, this blog cover the available options to implement change tracking over a Microsoft SQL Server database. It also discuss the pros and cons of each method. These methods are also mentioned by Ronen and Erland.

This blog talked about :
•“Crawling” tables
•Working with triggers
•Change Tracking
•Change Data Capture
•Query Notification

SQL Server: Overview of different methods for change tracking

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".