How to track down who access my table?

Question

How to track down who access my table?

joemai

Hall of Fame

Points: 3496
More actions
July 11, 2005 at 7:37 pm

#152461

I have a table, and I'd like to know for every 20' who is accessing it and which sql statement they are running. For example, I'd like to know who run a select, update, delete, or insert data into or against my table. Is this can be achieved? Thanks a million in advance.

Minh Vu

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The only way to track all those is to start a trace using the profiler. This will allow you to get all that information.

The downside is that this can cause some (minute to huge) overhead on the server. But depending on the situation this is often a very acceptable cost to pay.

faizjmc SSC Eights! Points: 881 More actions · Answer 2

U didn't specify any method to do the task..

What is the way to achieve it

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

R u talking to me??

The only way to track all those is to start a trace using the profiler. This will allow you to get all that information.

The downside is that this can cause some (minute to huge) overhead on the server. But depending on the situation this is often a very acceptable cost to pay.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

I'll piggy-back on Remi.

SQL Profiler is a tool that comes with SQL Server. If you have Enterprise Manager and Query Analyzer on your system, you likely have SQL Profiler as well. It can do traces of all database activity coming in to your SQL Server.

Also, if you're adventurous, you can use the trace stored procedures that are built into SQL Server (this is actually how Profiler works).

In Books Online you can find more information on SQL Profiler at:

Administering SQL Server | Monitoring Server Performance and Activity | Monitoring with SQL Profiler.

Here is the linke to the documentation on the MSDN site:

MSDN Library: Monitoring with SQL Profiler

K. Brian Kelley
@kbriankelley

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 5

An another way is to use SQL Server Agent to run sp_who or sp_who2 every 20'. you can collect the data then analyse it.

There is one drawback: neither sp_who nor profiler gives you the exact information about table usage. you can see the command issued in the profiler or the command type (select insert...) in sp_who.

If you want to see the exact command issued, then you have to use dbcc inputbuffer

Bye
Gabor

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

Unless it's a security related function or stored procedure (like sp_addlogin), Profiler will give you the exact command issued. You have to toggle SQL statement instead of SQL batch, however, as an event to monitor.

K. Brian Kelley
@kbriankelley