Any way to track who/when data was deleted ?

Hello,
I'm using merge replication with SQL Server 2000 on Windows 2003 server,
with MSDE2000A clients with anon pull subscriptions.
Some data has been (mysteriously) deleted and I would like to find out more
about who/when it was deleted.
Is there any way (system tables, transaction log ?) of determining who/when
certain records were deleted ?
I noticed that in my publication DB the MSmerge_tombstone table has a field
that shows either "user delete" or "system delete" - what is a System
Delete?
Thanks for your help.
Darren
No, you have to write custom triggers to track this information.
What you will get is the user that makes this change on the local server,
and then the account your merge agent runs under. So if you make a change on
your publisher, there is no way to have this user name transferred to the
subcriber, unless you also replicated an audit table.
User delete is when a user deletes a record, system delete is when a system
process (normally in response to a conflict) deletes the record.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Darren Wallace" <darren@.pcresources.com.au> wrote in message
news:eQdrh$FmEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm using merge replication with SQL Server 2000 on Windows 2003 server,
> with MSDE2000A clients with anon pull subscriptions.
> Some data has been (mysteriously) deleted and I would like to find out
more
> about who/when it was deleted.
> Is there any way (system tables, transaction log ?) of determining
who/when
> certain records were deleted ?
> I noticed that in my publication DB the MSmerge_tombstone table has a
field
> that shows either "user delete" or "system delete" - what is a System
> Delete?
> Thanks for your help.
> Darren
>
|||www.lumigent.com
"Darren Wallace" <darren@.pcresources.com.au> wrote in message
news:eQdrh$FmEHA.3524@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I'm using merge replication with SQL Server 2000 on Windows 2003 server,
> with MSDE2000A clients with anon pull subscriptions.
> Some data has been (mysteriously) deleted and I would like to find out
more
> about who/when it was deleted.
> Is there any way (system tables, transaction log ?) of determining
who/when
> certain records were deleted ?
> I noticed that in my publication DB the MSmerge_tombstone table has a
field
> that shows either "user delete" or "system delete" - what is a System
> Delete?
> Thanks for your help.
> Darren
>

Any way to track if a person does a select on a table?

We have a situation where we would like to know who is accessing our data.
We know we could make a user read only but we'd also like to know what they
did to retrieve the data.
I guess what I need is Query Profiler but limit it to a specific user. Since
the user will have read only privileges, I won't have to worry about them
changing the data.
I just want to know that they accessed it.
BTW - they will be doing this through an ODBC connection in Access.
TIA - Jeff.
I think you answered your own question Profile the database, filter the
user, save the trace to a table and query the table for the results...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>
|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>
|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

Any way to track if a person does a select on a table?

We have a situation where we would like to know who is accessing our data.
We know we could make a user read only but we'd also like to know what they
did to retrieve the data.
I guess what I need is Query Profiler but limit it to a specific user. Since
the user will have read only privileges, I won't have to worry about them
changing the data.
I just want to know that they accessed it.
BTW - they will be doing this through an ODBC connection in Access.
TIA - Jeff.I think you answered your own question :) Profile the database, filter the
user, save the trace to a table and query the table for the results...
--
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

Any way to track if a person does a select on a table?

We have a situation where we would like to know who is accessing our data.
We know we could make a user read only but we'd also like to know what they
did to retrieve the data.
I guess what I need is Query Profiler but limit it to a specific user. Since
the user will have read only privileges, I won't have to worry about them
changing the data.
I just want to know that they accessed it.
BTW - they will be doing this through an ODBC connection in Access.
TIA - Jeff.I think you answered your own question Profile the database, filter the
user, save the trace to a table and query the table for the results...
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||Consider creating a SQL Trace with the desired events and filters. You can
create such a trace using the Profiler GUI and then script/run the trace to
log to a file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>|||network packet sniffing is the most efficient/effective way to do this.
There are several products on the market that do this now.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"Mufasa" <jb@.nowhere.com> wrote in message
news:%23nOYhFqPIHA.3532@.TK2MSFTNGP04.phx.gbl...
> We have a situation where we would like to know who is accessing our data.
> We know we could make a user read only but we'd also like to know what
> they did to retrieve the data.
> I guess what I need is Query Profiler but limit it to a specific user.
> Since the user will have read only privileges, I won't have to worry about
> them changing the data.
> I just want to know that they accessed it.
> BTW - they will be doing this through an ODBC connection in Access.
> TIA - Jeff.
>

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?
Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:

>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:

>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?

any way to do record login failures?

sql2000 sp3a
i'd like to keep track of login failures in a table in addition to the
sql log. is there any way to do this?
i've created an alert for error 18456, login failed for user '%ls'.
if i configure the alert to call a job, how do i get that error message
into the job so that i can insert it into a table?
also, i'd like to record the hostname or ip address of the client
machine from which the login failure occurs. i know sysprocesses has
that info once a user gets logged in, but where is that info if the
login fails?Another option is to use a trace (or profiler) to monitor
for failed logins. You can import the trace file into a
table.
The IP and Host name won't be directly available for failed
logins. Host name isn't that reliable anyway as it's
controlled by the client. For the ip address, you would need
to capture this using a network tool.
-Sue
On Wed, 02 Jun 2004 09:04:30 -0500, ch <ch@.dontemailme.com>
wrote:
>sql2000 sp3a
>i'd like to keep track of login failures in a table in addition to the
>sql log. is there any way to do this?
>i've created an alert for error 18456, login failed for user '%ls'.
>if i configure the alert to call a job, how do i get that error message
>into the job so that i can insert it into a table?
>also, i'd like to record the hostname or ip address of the client
>machine from which the login failure occurs. i know sysprocesses has
>that info once a user gets logged in, but where is that info if the
>login fails?