Re: Database Table usage

Jack
This script I got from Dejan Sarka long time ago, try and see if it works
for you
For example, let's say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:

- SP:StmtCompleted and SQL: StmtCompleted events

- EventClass, TextData, ApplicationName and SPID columns

- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters

- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.

Start the trace, and create the following trigger using Query Analyzer:



CREATE TRIGGER TraceSelectTrigger ON SelectTrigger

FOR INSERT

AS

EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning



Now check how trigger works by performing couple of selects:



SELECT TOP 1 *

FROM Customers

SELECT TOP 1 *

FROM Orders

SELECT TOP 1 c.CustomerID

FROM Customers c INNER JOIN Orders o

ON c.CustomerID=o.CustomerID



With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).


"Jack Benny" <pkline_no_spam@xxxxxxxxxxxxxxxxxx> wrote in message
news:a7ho755ca8p0m5hi5hhvdmlutpirntbo0g@xxxxxxxxxx
Is there any way to track who accesses a particular table in a
database and when? We have a few tables in a database that we update
daily but we're not exactly sure anyone is still here using the
information. Is there some form of monitor I can set?


.

Relevant Pages

  • Re: SQL 2005 - audit SELECT statements?
    ... name in the Northwind database. ... Start the trace, and create the following trigger using Query Analyzer: ... FROM Customers ...
    (microsoft.public.sqlserver.security)
  • Re: i would like to see who executed a select statement on a specific table
    ... name in the Northwind database. ... and create the following trigger using Query Analyzer: ... FROM Customers ... FROM Customers c INNER JOIN Orders o ...
    (microsoft.public.sqlserver.security)
  • Re: Best way to represent a many to many with optionality?
    ... the trigger does not "compile" when I try to create it in Management Studio. ... Also remember that the FK constraints for the two relationships (shipping & mailing address) are set to NO ACTION, meaning by default SQL Server would not permit the delete. ... if exists (select * from deleted join customers ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger to populate table or database
    ... I am trying to write a trigger that does this: ... fields to be copied to another table (call it Master). ... CustName, then simply ignore. ... A more normal design would be that the customers ...
    (comp.databases.ms-sqlserver)
  • Re: trigger on select
    ... not provided us with the version of SQL Server you are using I copied/pasted his example works on SQL Server 2000 ... let's say we want to follow selects on the Customers table of the Northwind database. ... and create the following trigger using Query Analyzer: ... FROM Customers c INNER JOIN Orders o ...
    (microsoft.public.sqlserver.server)
Loading