Hi there!
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!
LaMoRt (cwei83@gmail.com) writes:
> 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?
A log reader tool might help you, although I would not really expect
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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
LaMoRt wrote:
> 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?
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.
How about DDL triggers in SQL 2005
"Erland Sommarskog"
news:Xns99EEF236F30C4Yazorman@127.0.0.1...
> LaMoRt (cwei83@gmail.com) writes:
>> 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?
>
> A log reader tool might help you, although I would not really expect
> 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, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
"BerndB"
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).
> "Erland Sommarskog"
> news:Xns99EEF236F30C4Yazorman@127.0.0.1...
>> LaMoRt (cwei83@gmail.com) writes:
>>> 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?
>>
>> A log reader tool might help you, although I would not really expect
>> 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, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
BerndB (bernd@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
On Nov 22, 6:13 am, Erland Sommarskog
> 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 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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.
LaMoRt (cwei83@gmail.com) writes:
> 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?
If the database is in full recovery, you could use a log reader. There
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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
On Nov 23, 6:32 am, Erland Sommarskog
> LaMoRt (cwe...@gmail.com) writes:
> > 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?
>
> If the database is in full recovery, you could use a log reader. There
> 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 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
Thanks will try it out..