Re: Update trigger or ?

Joerg Gempe (j_spam_filter_gempe@xxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: How to protect SQL Server Express database from reverse engineerin
    ... prevent anyone from reverse engineering our database. ... I say within reasonable limits, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: Problem with my SQL 2000 Maintenance plans.
    ... I wouldn't use maint plan for this. ... using the most recent update of Books Online). ... If you do have a problem in the database, you want to be alerted so you can do ... >> option will most probably be removed in next version of SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Setting Up SQL Security
    ... want that user to have complete access to a database. ... But if it's impedient that no information whatsoever is ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.security)
  • Re: sp_fkeys stored procedue is very slow
    ... tables and columns that 'table1' depends on ... Are there many tables in the database? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: how to create database
    ... ' Connection string, change server and database! ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.data.oledb)