Sql Server - Send an email when user selects a particular column of a table

Question

I am trying to send an email when a user selects a particular column of a table in sql server 2019.

For example:

Select user_name as Name from dbo.User

Once this query is executed I need to check if one of the select columns is "user_name", if yes then send an email to the DBA.

Has anyone done this before? Please share your findings.

Thanks in advance.

Answer 1

The only way to do that is with a "Server Audit".

It will be a little complicated. But you can create a SELECT audit on the table/column. Then create a job which monitors the audit and emails anything added to the audit in the last x minutes.

Please see:
https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15

and
https://www.mssqltips.com/sqlservertip/1649/auditing-select-statements-in-sql-server-2008/

Answer 2

Hi,

Like always there are ,multiple options which you can use for the task according to what fits you best in your specific case.

Option 1: (not recommended but working and in some VERY rare case can fits) Use computed column which based on SQLCLR function. The function can send the email every time the column is selected as the function is executed dynamically when the column is selected.

Option 2: Using Extended Event to monitor all SELECT actions on the specific table -> store the information in activities table or in a QUEUE including -> check the QUEUE/table every few seconds (can be done using JOB) -> parse the information and check if the specific column was selected -> send email asynchronously -> remove the message from the QUEUE or mark the row in the table as "used".

Option 3: (probably best for most cases like this) use SQL Server Audit feature - as the answer Tom Phillips already gave you

Important! Store the audit information in a table and use JOB to send emails once every few seconds/minutes/hours (as fit your needs). Do not send the email rightway. It is best to collect information every X time and send all emails

Answer 3

So what are you trying to achieve, really?

Tom and Ronen have offered a couple of options, but I don't like any of them. Well, it could be that I don't like the idea to send mail as such.

If this column is so sensitive that the DBA should have a mail every time it is selected, it should probably be locked down by revoking direct access on the table - or only this column. Access to the column can then be exposed through a stored procedure that also writes an audit record to a log table, and then you have a job to monitor that table to send mail. (The reason you don't want the procedure to send the mail is that this requires that the user has permissions.)

Then again, it all depends on what your real problem is.

Answer 4

Hi @Sql User ,

From your description, if the column is sensitive, suggest you using symmetric keys to encrypt this column, then only give few users permission to decrypt data using the symmetric key. I think this operation will better protect your data.

Please refer to the blog An overview of the column level SQL Server encryption to get detail steps.

---

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