0

I'm using SQL Server 2012. Is there a way to receive a notification (email/log etc..) if a database or table in a database is touched in anyway (e.g. CRUD via Stored Procs or tables opened via Management Studio)?

I've come across C2 audio tracing while trying to find a solution but that seems server wide, whereas I need it for one particular database. Is this even possible?

Thanks

Improve this question
2
  • If the database is touched in anyway and you want to get notified, why are you even allowing access to unauthorized people or people whom you do not trust ?
    – Kin Shah
    Mar 23, 2016 at 16:08
  • It's locked down to the few 'trusted' users, in this case sys admins (including me), but I'd still like to audit any actions should something happen in the future. It's more of an audit in case something bad happens more than something bad is likely to happen therefore we need to prevent it. To clarify, it's actually one particular table I'd like to monitor but if it has to be done at a database level, that's fine
    – TryNCode
    Mar 23, 2016 at 16:16

2 Answers 2

Reset to default
2

To clarify, it's actually one particular table I'd like to monitor

You should use SQL Database Audit.

Below is an example that you are looking for (based on your comments)

(Modify as per your criteria and needs)

CREATE DATABASE AUDIT SPECIFICATION [AW_Audit_HR_Employee_DML]

FOR SERVER AUDIT [Server_Audit_AW]

ADD(SELECT,INSERT,UPDATE,DELETE,EXECUTE ON HumanResources.Employee BY sys)

WITH (STATE =ON)

More info at : SQL 2012 Security Audit 2 (Database Audit)

Improve this answer
1
  • That seems like exactly what I need, thanks!
    – TryNCode
    Mar 23, 2016 at 16:39
0

Why do you care if a database is read by a user who has the rights to read it? I don't get that.

That being said, if you're only looking to audit a particular table for Update, Insert, Delete etc then why not create triggers on the table which send an alert via email whenever the event is triggered?

There's a wonderful answer to this here:

https://stackoverflow.com/questions/10755469/send-e-mail-from-a-trigger

I implemented it recently for our apps team whenever an engineer altered their arrival time at a job.

Populates an audit table, sends an email. Hey Presto.

Improve this answer
4
  • I would very much recommend against using a trigger to send email, or perform any other long running process. Any code in the trigger will need to complete prior to returning control back to the client.
    – Hannah Vernon ♦
    Mar 23, 2016 at 20:18
  • A much better way would be to use the trigger to write into a log table, or perhaps a Service Broker queue. Service Broker would allow you to send the email asynchronously.
    – Hannah Vernon ♦
    Mar 23, 2016 at 20:20
  • It's horses for courses. Log table is a great Audit Trail mechanism, email operates as an alert system. In my case it was implemented due to a third party app that allowed users to edit date fields and an email was only sent if the date was updated beyond a certain range - really, peace of mind should be driven by security and permissions and this sort of thing wouldn't be needed.
    – Molenpad
    Mar 23, 2016 at 20:23
  • all good points. Just be aware that generic answers may be seen by many users, not just the one who asked the question.
    – Hannah Vernon ♦
    Mar 23, 2016 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.