Solved

SQL audit table when someon has accessed information

Posted on 2009-07-01
10
187 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now