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

  • 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 '16 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 '16 at 16:16
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 | |
  • That seems like exactly what I need, thanks! – TryNCode Mar 23 '16 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 | |
  • 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. – Max Vernon Mar 23 '16 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. – Max Vernon Mar 23 '16 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 '16 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. – Max Vernon Mar 23 '16 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.