Solved

SQL audit table when someon has accessed information

Posted on 2009-07-01
10
196 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
if and else in stored procedure 19 42
SQL Trigger selecting another database 4 33
SQL Server 2012 express 24 36
TSQL mapping detailed records to group records 9 44
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

919 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

18 Experts available now in Live!

Get 1:1 Help Now