Solved

SQL audit table when someon has accessed information

Posted on 2009-07-01
10
214 Views
Last Modified: 2012-05-07
Im trying to find a way to audit a table and check when someone access specific information on it.

Looks like trigger are made for DML. UPdate Inserts and deletes.

Can someone direct me in the right path to create some kind of mechanism to write to a table/log or an alert when someone access specific data on a table. I am mainly concerned on columns. If anyone is able to run a query against it, I need to have it logged.

Thanks.
0
Comment
Question by:quippee
  • 5
  • 2
  • 2
10 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 300 total points
ID: 24755224
The only way to do this is to write stored procedures that return the data and track when people run them...otherwise you really need to restrict this sort of thing through your security setup.  There is no trigger for when data is read.
0
 
LVL 15

Assisted Solution

by:mohan_sekar
mohan_sekar earned 200 total points
ID: 24755399
Another option is to use SQL Profiler, but you do not want it running all the time on your production server
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24755446
I suspect the reason you're asking thisproblem because your users are able to run any SELECT statements they want on your DB, right?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:quippee
ID: 24755448
We have secured the data, but we need to audit the table and have some sort of report to proof is doing what its suppose to be doing. I have enabled SQLprofiler for that table only, but if Im going that route I have to mine the data from the traces.

Chapmandew, the data is already being access by store procedures but if we have some kind of security breach we would like to know if anyone else is accessing it outside the SP. I guess there is no easy way without 3rd party tools.

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24755470
no way to do it, really...other than throughprofiler, and that is NOT a solution to this.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24755493
The only way to "ensure" that if someone were to get in, that they wouldn't be able to access the data....this can be done carefully through testing and tweaking your security setup.
0
 

Author Comment

by:quippee
ID: 24914038
Thanks. I will start playing with SQL profiler and see how that goes. its only one table. So I will give it a shot. security wise we are good to go. I just need to prove that is working lol.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24914069
Now reason for a B grade here....
0
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24914940
If you are going to start playing with SQL Profiler, which is what I suggested, why a "B" grade then?
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PERFORMANCE OF SQL QUERY 13 75
Truncate vs Delete 63 107
Following an example - removing duplicate strings 4 60
Problem with SqlConnection 4 176
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question