How can I find out who is running dangerous queries

[Miles]

I recently discovered that some one (or application) updated all the
rows in the database. Looks like a query was missing a where clause.
It was probably one of the applications, but reviews of the code show
that a where clauses is always used, or so it appears. Anyway, I set
up a trigger to capture every update to a particular table that
recorded who did what and when. I created the trigger (on insert and
update) on a table and in it I use the new fn_get_sql function that
comes with SQL Server 2000 SP3. It looks like this:

--------------------------------
CREATE TRIGGER Update_Last_Modified ON [dbo].[MYTABLENAME]
FOR UPDATE, INSERT
AS
BEGIN
SET NOCOUNT ON
DBCC TRACEON (2861)

DECLARE @Qry nvarchar(4000)
DECLARE @handle binary(20)

SELECT @handle = sql_handle
FROM master..sysprocesses
WHERE spid = @@SPID

SET @QRY = (SELECT CONVERT(nvarchar(4000), [text]) FROM
::fn_get_sql(@handle))

UPDATE MYTABLENAME
SET DATE_LAST_MODIFIED = GETDATE(),
LAST_COMMAND = @QRY,
LAST_USER = SYSTEM_USER
FROM inserted
WHERE MYTABLENAME.UID= Inserted.UID
END

----------------------------------

It was previously coded to use DBCC INPUTBUFFER, and it worked fine,
but I was limited to the first 255 characters of the command, which
prevented me from seeing the critical parts, like the where clause!
When I modified the trigger to use fn_get_sql, all I ever see is the
entire text of the create trigger command. Maybe I should use an
entirely different approach. I'm open to ideas.

Thanks very much in advance for your help!

Miles
_________________

[Erland Sommarskog]

Miles (mi***********@hotmail.com) writes:

It was previously coded to use DBCC INPUTBUFFER, and it worked fine,
but I was limited to the first 255 characters of the command, which
prevented me from seeing the critical parts, like the where clause!
When I modified the trigger to use fn_get_sql, all I ever see is the
entire text of the create trigger command. Maybe I should use an
entirely different approach. I'm open to ideas.

Yes, the idea with fn_get_sql is to get the currently executing statement
of a procedure. And for a process that introspects itself, the current
statement will be the statement it queries sysprocesses. So in your
case DBCC INPUTBUFFER is a better bet.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp