We help IT Professionals succeed at work.

SQL audit table when someon has accessed information

quippee
quippee asked
on
274 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.
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
I suspect the reason you're asking thisproblem because your users are able to run any SELECT statements they want on your DB, right?

Author

Commented:
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.

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
no way to do it, really...other than throughprofiler, and that is NOT a solution to this.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
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.

Author

Commented:
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.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
Now reason for a B grade here....
If you are going to start playing with SQL Profiler, which is what I suggested, why a "B" grade then?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.