Posted by Erland Sommarskog on 04/23/07 21:52

Joerg Gempe (j_spam_filter_gempe@gmx.de) writes:
> I've a problem where some data gets updated but I don't know which
> process (SP) is responsible for it (it's an old installation which I've
> taken over).
> Is it somehow possible to know which process/user/SP performs an update
> on a special table/column?

This query:

SELECT o.name, o2.name, c.name
FROM sysobjects o
JOIN sysdepends d ON o.id = d.id
JOIN sysobjects o2 ON d.depid = o2.id
JOIN syscolumns c ON d.depid = c.id
AND d.depnumber = c.colid
WHERE o2.name = 'yourtbl'
AND c.name = 'yourcol'
AND d.resultobj = 1

may return the information you need. I say may, because the dependency
information in a database is rarely complete. This is because if you
drop and recreate a table, without reloading the stored procedures,
the depencies are lost.

If it's possible for you to build the database from scripts, and
making sure that procedures and triggers are built after all tables,
then your odds are better.

> I was thinking about to implement an update trigger but unfortunately
> I've no idea how to figure out the "parent process" which was
> responsible for the update.

DBCC INPUTBUFFER could address this, but:

1) it requires the user to have sysadm privileges.
2) it will only show you the command sent from the client. If procedures
nest in several levels, this information may not be sufficient.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]