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

Sql User 1 Reputation point
2021-12-07T15:42:41.04+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,263 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Tom Phillips 17,721 Reputation points
    2021-12-07T17:44:24.49+00:00

    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/


  2. Ronen Ariely 15,186 Reputation points
    2021-12-07T18:27:04.233+00:00

    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


  3. Erland Sommarskog 107.2K Reputation points
    2021-12-07T22:43:25.873+00:00

    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.

    0 comments No comments

  4. CathyJi-MSFT 21,131 Reputation points Microsoft Vendor
    2021-12-08T07:14:52.95+00:00

    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".

    0 comments No comments