Finding the user who do update in Query Analyser?
LaMoRt
Is there any way to find the user who did some transaction of update
in query analyser in certain time ?
The update was done manually in query analyser and is not through
system.
Is there any way to check it?
Any command or software to check this data as when certain table was
updated?
Thanks for the help!
Erland Sommarskog
the application name to be in the log. There are several vendors that
market log readers: Lumigent, Log PI, Red Gate to name a few.
--
Erland Sommarskog, SQL Server MVP, esq...@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
Ed Murphy
You can create a trigger on one table that adds rows to a second audit
table. See http://doc.ddart.net/mssql/sql70/create_8.htm
Note that a single INSERT/UPDATE/DELETE may affect multiple rows at
once, so your trigger logic must account for this possibility.
You can get the current user's identity from USER_NAME() or
SYSTEM_USER() or SESSION_USER() - unfortunately I don't understand
how the last two are different from the first, so someone else will
have to fill that in.
BerndB
"Erland Sommarskog" <esq...@sommarskog.se> schrieb im Newsbeitrag
news:Xns99EEF236F...@127.0.0.1...
Greg D. Moore (Strider)
news:fi16t9$drl$1...@tamarack.fernuni-hagen.de...
> How about DDL triggers in SQL 2005
Wouldn't really help here. (Technically you want DML triggers, which were
available in SQL 2000 also).
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Erland Sommarskog
> How about DDL triggers in SQL 2005
With all sorts of triggers you can capture a lot - but only if you plan
ahead. I understoof the original question as that what shouldn't happen
had already happened.
LaMoRt
> BerndB (be...@fernuni-hagen.de) writes:
> > How about DDL triggers in SQL 2005
>
> With all sorts of triggers you can capture a lot - but only if you plan
> ahead. I understoof the original question as that what shouldn't happen
> had already happened.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Yes, it is a data that has been updated and i want to track back the
person
who did the transaction. Any way to find it in this kind of situation.
I'm using SQL2000 actually. So anything that can help me for this?
Thanks.
Erland Sommarskog
> Yes, it is a data that has been updated and i want to track back the
> person
> who did the transaction. Any way to find it in this kind of situation.
> I'm using SQL2000 actually. So anything that can help me for this?
are a couple on the market, and I've lost track of them all. Lumigent
was the first in this field, and for a long time the only player on
the market. Log PI has also been around for a while now. I see that
Red Gate has SQL Log Rescue which currently is free.
If the database is in simple recovery, or you have truncated the log
without backing it up since this update appeared, you can forget about it.
And I should add that even with a log reader, it can be quite a tedious
task to find the culprit.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
LaMoRt
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks will try it out..