auditting select statement

We have triggers written for insert/update/deletes of data, now there is a
new requirement to audit select statements ran against the database.
I know SQL Profiler shows the select statements ran. I was wondering if
anyone has suggestions on how to somehow use that function(or any other idea
)
and incorparate it into tracking all select statements?Tracey wrote:
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other id
ea)
> and incorparate it into tracking all select statements?
We monitor everything that happens in our production databases, by
running server-side traces 24x7 into rotating trace log files. Easily
done using sp_trace_create, etc., providing you have the disk space to
store the accumulating trace log files.
You get the added benefit of being able to analyze your database
activity to look for poorly performing queries.|||SQL Server does not track this activity. Profiler can see it because it
views all commands going into the database. Some options:
(1) If you deny SELECT access to tables and views, and force all access
through stored procedures, it is trivial to log this.
(2) You can have a trace running all the time that dumps data into trace
table(s).
(3) Or you can look at 3rd party tools (in which case, you won't have to
write all of the reporting over the trace table(s)). For example,
Lumigent's Audit DB or Log Explorer. See
http://www.aspfaq.com/search.asp?q=lumigent
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Tracey
Take a look at Dejan's example
For example, lets say we want to follow selects on the Customers table of
the Northwind database. Create a trace with only the following settings:
- SP:StmtCompleted and SQL: StmtCompleted events
- EventClass, TextData, ApplicationName and SPID columns
- DatabaseID Equals 6 (DB_ID() of the Northwind database) and
TextData Like select%customers% filters
- Name the trace SelectTrigger and save it to a table with the same
name in the Northwind database.
Start the trace, and create the following trigger using Query Analyzer:
CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
FOR INSERT
AS
EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
warning
Now check how trigger works by performing couple of selects:
SELECT TOP 1 *
FROM Customers
SELECT TOP 1 *
FROM Orders
SELECT TOP 1 c.CustomerID
FROM Customers c INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
With Event Viewer, check whether you got two warnings in the Application log
for the 1st and the 3rd queries (the 2nd should be filtered out).
"Tracey" <Tracey@.discussions.microsoft.com> wrote in message
news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
> We have triggers written for insert/update/deletes of data, now there is a
> new requirement to audit select statements ran against the database.
> I know SQL Profiler shows the select statements ran. I was wondering if
> anyone has suggestions on how to somehow use that function(or any other
> idea)
> and incorparate it into tracking all select statements?|||Hi Uri,
Seems good.. But is it fool -proof.
what will happen if I query this way.. just for an arguement
SELECT TOP 1 [Customers List].Customer_ID
FROM Orders as [Customers List]
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Uri Dimant" wrote:

> Tracey
> Take a look at Dejan's example
> For example, let’s say we want to follow selects on the Customers table
of
> the Northwind database. Create a trace with only the following settings:
> - SP:StmtCompleted and SQL: StmtCompleted events
> - EventClass, TextData, ApplicationName and SPID columns
> - DatabaseID Equals 6 (DB_ID() of the Northwind database) and
> TextData Like select%customers% filters
> - Name the trace SelectTrigger and save it to a table with the sa
me
> name in the Northwind database.
> Start the trace, and create the following trigger using Query Analyzer:
>
> CREATE TRIGGER TraceSelectTrigger ON SelectTrigger
> FOR INSERT
> AS
> EXEC master.dbo.xp_logevent 60000, 'Select from Customers happened!',
> warning
>
> Now check how trigger works by performing couple of selects:
>
> SELECT TOP 1 *
> FROM Customers
> SELECT TOP 1 *
> FROM Orders
> SELECT TOP 1 c.CustomerID
> FROM Customers c INNER JOIN Orders o
> ON c.CustomerID=o.CustomerID
>
> With Event Viewer, check whether you got two warnings in the Application l
og
> for the 1st and the 3rd queries (the 2nd should be filtered out).
>
> "Tracey" <Tracey@.discussions.microsoft.com> wrote in message
> news:F7DB3821-3537-493A-A19F-8C17D5A44799@.microsoft.com...
>
>|||Hi
This one will also be logged to the Apllication Viewer, however I agree that
this method is not perfect
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:499B5B18-A216-4732-BF8A-6B295C23D7D1@.microsoft.com...
> Hi Uri,
> Seems good.. But is it fool -proof.
> what will happen if I query this way.. just for an arguement
> SELECT TOP 1 [Customers List].Customer_ID
> FROM Orders as [Customers List]
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Uri Dimant" wrote:
>

Auditing without having to use SQL Profiler

I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.
You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_side...sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.c om...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.
|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. More
> info and code at:
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.c om...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.

Auditing without having to use SQL Profiler

I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. More
> info and code at:
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.

Auditing without having to use SQL Profiler

I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.You could trace using trace stored procedures. No need to use Profiler. More
info and code at:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Dipak Patel" <dipak99@.hotmail.com> wrote in message
news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
I want to audit users logging on/off, creating/dropping/modifying
objects, etc, etc.
I know this can all be done using SQL Profiler, however this requires
SQL Profiler to be running all the time.
Are there any database system tables which hold this sort of
information, i.e. modified date of objects, etc, so that I can
schedule jobs to monitor user activity, as and when I want? (This
sort of thing can be done in Oracle)
I have looked at sysobjects and there is only create date in there.|||OK thanks for that. i will try it out at some point.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message news:<#I62DHkSEHA.3812@.TK
2MSFTNGP11.phx.gbl>...
> You could trace using trace stored procedures. No need to use Profiler. Mo
re
> info and code at:
> http://vyaskn.tripod.com/server_sid..._sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Dipak Patel" <dipak99@.hotmail.com> wrote in message
> news:30bbec2c.0406040636.3ae9786f@.posting.google.com...
> I want to audit users logging on/off, creating/dropping/modifying
> objects, etc, etc.
> I know this can all be done using SQL Profiler, however this requires
> SQL Profiler to be running all the time.
> Are there any database system tables which hold this sort of
> information, i.e. modified date of objects, etc, so that I can
> schedule jobs to monitor user activity, as and when I want? (This
> sort of thing can be done in Oracle)
> I have looked at sysobjects and there is only create date in there.

auditing the table

Hi,
I would like to audit table but I do not want to use
profiler. I need to know NT user name not just sql server
standard login for the user. The problem is that somebody
ocasssionally deletes the data and I would like to find
out who is doing it.
Any view is appreciated.You could create a TRIGGER on the table. eg :-
CREATE TRIGGER reminder
ON titles
FOR DELETE
AS
EXEC master..xp_sendmail 'ToYou',
'Someone just deleted a row'
GO
this could easily be enhanced to log to a table, you can capture the user
name of who did the deletion by capturing USER_NAME
HTH
Ryan Waight, MCDBA, MCSE
"Mirna" <mstojsic@.hotmail.com> wrote in message
news:2739901c38f3d$83e5d340$a601280a@.phx.gbl...
> Hi,
> I would like to audit table but I do not want to use
> profiler. I need to know NT user name not just sql server
> standard login for the user. The problem is that somebody
> ocasssionally deletes the data and I would like to find
> out who is doing it.
> Any view is appreciated.
>

Auditing tables

HI!
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swati
swati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>
|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot
>
|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>
|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>
|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table[vbcol=seagreen]
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot
>

Auditing tables

HI!
I want to audit some collumns of one of my huge table . I wrote a trigger
for copying updated data and original data in audit table.
But when I try to update multiple rows , it gives error of "cannot update
multiple rows. "
Is there any easy way of auditing tables without using triggers . I donot
want to use C2-audit .
Regards,
Swatiswati
create trigger tru_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
> HI!
> I want to audit some collumns of one of my huge table . I wrote a trigger
> for copying updated data and original data in audit table.
> But when I try to update multiple rows , it gives error of "cannot
update
> multiple rows. "
> Is there any easy way of auditing tables without using triggers . I donot
> want to use C2-audit .
> Regards,
> Swati
>
>
>|||Thanks!! It works properly .
Regards,
Swati
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> swati
> create trigger tru_MyTable on MyTable after update
> as
> if @.@.ROWCOUNT = 0
> return
> insert MyAuditTable
> select
> i.ID
> , d.MyColumn
> , i.MyColumn
> from
> inserted i
> join
> deleted d on d.ID = o.Id
> go
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:uNbsEK1uEHA.1308@.TK2MSFTNGP09.phx.gbl...
trigger[vbcol=seagreen]
> update
donot[vbcol=seagreen]
>|||Hi ,
I am trying trigger for after delete /insert and update .
using below trigger , unable to insert data in myaudittable ..
Pls suggest , what changes should I make ?
Regards
"swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
> Thanks!! It works properly .
> Regards,
> Swati
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ebvGMQ1uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> trigger
> donot
>|||What error are you getting? Please post your actual trigger code and table
DDL.
Hope this helps.
Dan Guzman
SQL Server MVP
"swati" <swati.zingade@.ugamsolutions.com> wrote in message
news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> I am trying trigger for after delete /insert and update .
> using below trigger , unable to insert data in myaudittable ..
> Pls suggest , what changes should I make ?
> Regards
>
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message news:...
>|||Thanks dan . There was some silly mistake I did in coding . Instead of using
full outer joins ,I was using normal joins ,so was not able to get the
deleted records .Now it's working fine.
regards,
Swati.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ulLkEBCvEHA.2196@.TK2MSFTNGP14.phx.gbl...
> What error are you getting? Please post your actual trigger code and
table
> DDL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "swati" <swati.zingade@.ugamsolutions.com> wrote in message
> news:O$InOPAvEHA.1520@.TK2MSFTNGP11.phx.gbl...
"cannot[vbcol=seagreen]
>

auditing sql server

I need to enable c2 auditing on our SQL SERVER 2000 database, but it's not
working.
I set the parameters:show advanced options and 'c2 audit mode' to 1. I
restarted SQL server. I am a DBA,so my privileges should be appropriate. I
went into SQL analyzer and ran a few DML statements. I am not seeing an audit
file under the mssql serverdata directory. Somethings missing and I do not
know what it is.
I would appreciate any suggestions you may have.
Did you run RECONFIGURE?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:3B04E334-80AA-475E-B677-728B1932A173@.microsoft.com...
>I need to enable c2 auditing on our SQL SERVER 2000 database, but it's not
> working.
> I set the parameters:show advanced options and 'c2 audit mode' to 1. I
> restarted SQL server. I am a DBA,so my privileges should be appropriate. I
> went into SQL analyzer and ran a few DML statements. I am not seeing an audit
> file under the mssql serverdata directory. Somethings missing and I do not
> know what it is.
> I would appreciate any suggestions you may have.

Auditing SP Execute.

To meet audit requirements, I have to maintain an audit log of when a
particular sp was execute and by whom. I am not willing to use the c2
option for just one stored procedure. There are 2 users who can run
the stored procedure from enterprise manager or query analyser (others
run it from an app). Any suggetions?
Thank You,
-pranayYou can run a trace that is filtered only on this sp. That is the easiest
and least expensive way. Or you would have to buy one of the 3rd party log
tools to view the contents of the log.
Andrew J. Kelly SQL MVP
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
> To meet audit requirements, I have to maintain an audit log of when a
> particular sp was execute and by whom. I am not willing to use the c2
> option for just one stored procedure. There are 2 users who can run
> the stored procedure from enterprise manager or query analyser (others
> run it from an app). Any suggetions?
> Thank You,
> -pranay
>|||I want to capture the user name and the time (using sql). i dont want
to run profiler for ever. When someone runs the sp may be i can get
the userid and insert it into a table.|||Without monitoring the activity with either trace or a 3rd party tool there
is no way to do this in Sql2000.
Andrew J. Kelly SQL MVP
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109187761.063534.20330@.z14g2000cwz.googlegroups.com...
>I want to capture the user name and the time (using sql). i dont want
> to run profiler for ever. When someone runs the sp may be i can get
> the userid and insert it into a table.
>|||As long as the users only have execute permissions on the proc i.e. they
can't change it, then just add some code at the start of the proc to log to
a table passing getdate() and suser_sname() to capture the time and user
executing the proc. This assumes that the app doesn't use a single service
account (in which case you won't be able to get the user name without
modifying the app to stuff it into context_info)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Pranay Pandya" <ppandya@.gmail.com> wrote in message
news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
> To meet audit requirements, I have to maintain an audit log of when a
> particular sp was execute and by whom. I am not willing to use the c2
> option for just one stored procedure. There are 2 users who can run
> the stored procedure from enterprise manager or query analyser (others
> run it from an app). Any suggetions?
> Thank You,
> -pranay
>|||Andrew J. Kelly wrote:
> Without monitoring the activity with either trace or a 3rd party tool ther
e
> is no way to do this in Sql2000.
>
Of course he can.
create table AuditLog (WhenItRan datetime, WhoRanIt sysname)
go
Add to beginning of SP:
insert into AuditLog values (getdate(), suser_sname())|||Ahh yes. My original reading was he was looking to trace the sp_executesql
sp but I think I was mistaken. If it is a user sp then sure.
Andrew J. Kelly SQL MVP
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OcM6uPfGFHA.3076@.tk2msftngp13.phx.gbl...
> As long as the users only have execute permissions on the proc i.e. they
> can't change it, then just add some code at the start of the proc to log
> to a table passing getdate() and suser_sname() to capture the time and
> user executing the proc. This assumes that the app doesn't use a single
> service account (in which case you won't be able to get the user name
> without modifying the app to stuff it into context_info)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Pranay Pandya" <ppandya@.gmail.com> wrote in message
> news:1109178725.456740.135850@.z14g2000cwz.googlegroups.com...
>|||Thank you Every for the posts. I am going to create the audit table and
log it.
Andrew J. Kelly wrote:
> Ahh yes. My original reading was he was looking to trace the
sp_executesql[vbcol=seagreen]
> sp but I think I was mistaken. If it is a user sp then sure.
> --
> Andrew J. Kelly SQL MVP
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OcM6uPfGFHA.3076@.tk2msftngp13.phx.gbl...
they[vbcol=seagreen]
to log[vbcol=seagreen]
and[vbcol=seagreen]
single[vbcol=seagreen]
name[vbcol=seagreen]
when a[vbcol=seagreen]
the c2[vbcol=seagreen]
run[vbcol=seagreen]
(others[vbcol=seagreen]

auditing SELECT statements

Is there a way to audit SELECT statements on a table? A
database has a few hundred tables and I was asked to find
the tables which are not used in the application. The same
question for UPDATE, DELETE and INSERT (it is not
desirable to use triggers to trace DML statements).
Thanks in advance, GregYou can use SQL Profiler. You use SQL Profiler to define your trace/audit
first. In the definition, you can filter for SELECT and/or the table name.
Then, you can generate a T-SQL script, using the Script Trace ... option
under the File menu, to run on the server side without any GUI frontend.
Check out the following articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;283790&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;283786&Product=sql2k
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:04f001c39e76$54347780$a401280a@.phx.gbl...
> Is there a way to audit SELECT statements on a table? A
> database has a few hundred tables and I was asked to find
> the tables which are not used in the application. The same
> question for UPDATE, DELETE and INSERT (it is not
> desirable to use triggers to trace DML statements).
> Thanks in advance, Greg|||Hmmm...I've never seen that ability before -- that's very cool!
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:e$hF6CpnDHA.1072@.TK2MSFTNGP09.phx.gbl...
> You can use SQL Profiler. You use SQL Profiler to define your trace/audit
> first. In the definition, you can filter for SELECT and/or the table name.
> Then, you can generate a T-SQL script, using the Script Trace ... option
> under the File menu, to run on the server side without any GUI frontend.
> Check out the following articles:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283790&Product=sql2k
> http://support.microsoft.com/default.aspx?scid=kb;en-us;283786&Product=sql2k
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Greg" <anonymous@.discussions.microsoft.com> wrote in message
> news:04f001c39e76$54347780$a401280a@.phx.gbl...
> > Is there a way to audit SELECT statements on a table? A
> > database has a few hundred tables and I was asked to find
> > the tables which are not used in the application. The same
> > question for UPDATE, DELETE and INSERT (it is not
> > desirable to use triggers to trace DML statements).
> >
> > Thanks in advance, Greg
>|||If you have access to the source code of the application you can try
SQLClean by www.lockwoodtech.com . I haven't tried it myself though.
--
Jacco Schalkwijk
SQL Server MVP
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:04f001c39e76$54347780$a401280a@.phx.gbl...
> Is there a way to audit SELECT statements on a table? A
> database has a few hundred tables and I was asked to find
> the tables which are not used in the application. The same
> question for UPDATE, DELETE and INSERT (it is not
> desirable to use triggers to trace DML statements).
> Thanks in advance, Greg|||Linchi, is there a way to get ObjectName in the trace?
When i define a trace definition i can add ObjectName,
ObjectID or OwnerName but they are always empty. Is there
a way to populate them? Thanks for your help. Greg
>--Original Message--
>You can use SQL Profiler. You use SQL Profiler to define
your trace/audit
>first. In the definition, you can filter for SELECT
and/or the table name.
>Then, you can generate a T-SQL script, using the Script
Trace ... option
>under the File menu, to run on the server side without
any GUI frontend.
>Check out the following articles:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;283790&Product=sql2k
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;283786&Product=sql2k
>--
>Linchi Shea
>linchi_shea@.NOSPAMml.com
>
>"Greg" <anonymous@.discussions.microsoft.com> wrote in
message
>news:04f001c39e76$54347780$a401280a@.phx.gbl...
>> Is there a way to audit SELECT statements on a table? A
>> database has a few hundred tables and I was asked to
find
>> the tables which are not used in the application. The
same
>> question for UPDATE, DELETE and INSERT (it is not
>> desirable to use triggers to trace DML statements).
>> Thanks in advance, Greg
>
>.
>

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Auditing Select Statements

Hello,
I need to find out if there is a way to audit selects against a certain
table. I know you can use triggers to monitor inserts, updates and
deletes but is there a way to monitor when someone selects specific
data? Thanks!
Rachael
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Go to Lumigents web site... www.lumigent.com... I beleive they have an
auditing tool that does what you wish..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rachael" <rachael_faber@.hotmail.com> wrote in message
news:eu0FLAVVEHA.584@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need to find out if there is a way to audit selects against a certain
> table. I know you can use triggers to monitor inserts, updates and
> deletes but is there a way to monitor when someone selects specific
> data? Thanks!
> Rachael
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Auditing Reports

We have a many reports in many folders. We are now at the point that we need
some kind of tool to audit all these reports for following:
Security
connectionStrings
Data Sources
ReportLocations
I saw in ReportServer database there are all kind of tables which has all of
these information, but I was wondering if there is a solution out there to
query those tables to achive this goal.
Thanks
VipulHi,
Ofcourse you can use those system tables, but with care. each table is
useful for collecting information. Infact the table name are self explanatory
and you can query it for info.
Amarnath
"Vipul Shah" wrote:
> We have a many reports in many folders. We are now at the point that we need
> some kind of tool to audit all these reports for following:
> Security
> connectionStrings
> Data Sources
> ReportLocations
> I saw in ReportServer database there are all kind of tables which has all of
> these information, but I was wondering if there is a solution out there to
> query those tables to achive this goal.
> Thanks
> Vipul
>|||Do you know of any available sample queries to query those tables?
"Amarnath" wrote:
> Hi,
> Ofcourse you can use those system tables, but with care. each table is
> useful for collecting information. Infact the table name are self explanatory
> and you can query it for info.
> Amarnath
>
> "Vipul Shah" wrote:
> > We have a many reports in many folders. We are now at the point that we need
> > some kind of tool to audit all these reports for following:
> > Security
> > connectionStrings
> > Data Sources
> > ReportLocations
> >
> > I saw in ReportServer database there are all kind of tables which has all of
> > these information, but I was wondering if there is a solution out there to
> > query those tables to achive this goal.
> >
> > Thanks
> > Vipul
> >

Auditing in SQL Server 2005 - Need Help

We are currently looking at providing comprehensive audit support for our
corporate treasury application Vruksha. This product is being ported from SQ
L
Server 2000/VB6 to SQL Server 2005/VB.Net. Our existing audit feature in the
SQL Server 2000/VB application is limited to maintaining copies of all
records that are edited and deleted in tables that have the same structure a
s
the original data tables with additional fields to capture modifier details.
With this approach however, we have had issues with providing contextual and
meaningful audit information to the user. In the ported version, we wish to
provide a more complete audit infrastructure, with the following objectives:
1. Generate audit records for all defined events, whether they occur through
the application, SQL Server client or otherwise.
2. Capture sufficient information in audit records to establish what events
occurred, the sources of the events, and the outcomes of the events.
3. Provide the capability to include additional, more detailed information
in the audit records for audit events identified by type, location, or
subject.
4. Provide the capability to centrally manage the content of audit records
generated by individual components throughout the system.
5. Provide time stamps for use in audit record generation.
6. Protect audit information and audit tools from unauthorized access,
modification, and deletion.
7. Provide an audit reduction and report generation capability.
8. Provide the capability to automatically process audit records for events
of interest based upon selectable, event criteria.
9. Create, maintain, and protect from modification or unauthorized access or
destruction of an audit trail of accesses to the objects it protects.
10. Protect audit data so that read access to it is limited to those who are
authorized.
11. Record the following types of events: use of identification and
authentication mechanisms, introduction of objects into a user's address
space (e.g., file open, program initiation), deletion of objects, and action
s
taken by computer operators and system administrators and/or system security
officers and other security relevant events.
12. Audit any override of human-readable output markings.
13. Identify the date and time of the event, user, type of event, and
success or failure of the event. For identification and authentication
events, the origin of request (e.g., terminal ID) shall be included in the
audit record. For events that introduce an object into a user's address spac
e
and for object deletion events, the audit record shall include the name of
the object and the object's label.
14. The system administrator shall be able to selectively audit the actions
of any one or more users based on individual identity and/or object label.
15. Raise alarms whenever a threshold is reached with respect to an auditing
system resource (disk space in audit log volume) or when auditing has been
turned off (either inadvertently or deliberately).
16. Provide details of databases/schemas/tables accessed and modified by the
users and the administrator
17. Provide details of exceptional activity in certain columns (eg. Salary
column incremented by more than 20%)
With this background, we have the following questions:
1. Is it efficient to maintain auditing records in a separate audit table(s)
or maintain it as part of the same table? What has been the collective
experience of developers?
2. Is there a white paper on auditing in SQL Server 2005/.Net?
3. What is the native database support for auditing in SQL Server 2005?
Would they address our concerns?
4. What are the readily available .Net components for auditing that we may
use in this scenario?> 1. Is it efficient to maintain auditing records in a separate audit
> table(s)
> or maintain it as part of the same table? What has been the collective
> experience of developers?
You can create an Audit_Table to handle the changes of (Sales table)
for this particular table
create trigger tru_MyTable on Sales after update
as
if @.@.ROWCOUNT = 0
return
insert Audit_Table
select i.ID, d.MyColumn, i.MyColumn
from inserted i join deleted d on d.ID = i.Id
go

> 2. Is there a white paper on auditing in SQL Server 2005/.Net?
Start with BOL (Books On Line)

> 3. What is the native database support f
or auditing in SQL Server 2005? > Would they address our concerns?
Take look into the BOL
CREATE TRIGGER STOP_DDL_on_Table_and_PROC
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATA
BASE
AS
PRINT 'You are not allowed to CREATE,ALTER and DROP any Databases'
ROLLBACK;
USE MASTER
GO
CREATE DATABASE TEST
GO
ALTER DATABASE DDLTRTEST MODIFY FILE
(
NAME = DDLTRTEST,
FILENAME='D:\DDLTRTEST.MDF')
GO
USE MASTER
GO
DROP DATABASE DDLTRTEST
GO

> 4. What are the readily available .Net components for auditing that we may
> use in this scenario?
Sorry, I don't know so much .NET
"Peri" <Peri@.discussions.microsoft.com> wrote in message
news:F2025A73-32AA-41B6-9FF0-2030B5B270CA@.microsoft.com...
> We are currently looking at providing comprehensive audit support for our
> corporate treasury application Vruksha. This product is being ported from
> SQL
> Server 2000/VB6 to SQL Server 2005/VB.Net. Our existing audit feature in
> the
> SQL Server 2000/VB application is limited to maintaining copies of all
> records that are edited and deleted in tables that have the same structure
> as
> the original data tables with additional fields to capture modifier
> details.
> With this approach however, we have had issues with providing contextual
> and
> meaningful audit information to the user. In the ported version, we wish
> to
> provide a more complete audit infrastructure, with the following
> objectives:
> 1. Generate audit records for all defined events, whether they occur
> through
> the application, SQL Server client or otherwise.
> 2. Capture sufficient information in audit records to establish what
> events
> occurred, the sources of the events, and the outcomes of the events.
> 3. Provide the capability to include additional, more detailed information
> in the audit records for audit events identified by type, location, or
> subject.
> 4. Provide the capability to centrally manage the content of audit records
> generated by individual components throughout the system.
> 5. Provide time stamps for use in audit record generation.
> 6. Protect audit information and audit tools from unauthorized access,
> modification, and deletion.
> 7. Provide an audit reduction and report generation capability.
> 8. Provide the capability to automatically process audit records for
> events
> of interest based upon selectable, event criteria.
> 9. Create, maintain, and protect from modification or unauthorized access
> or
> destruction of an audit trail of accesses to the objects it protects.
> 10. Protect audit data so that read access to it is limited to those who
> are
> authorized.
> 11. Record the following types of events: use of identification and
> authentication mechanisms, introduction of objects into a user's address
> space (e.g., file open, program initiation), deletion of objects, and
> actions
> taken by computer operators and system administrators and/or system
> security
> officers and other security relevant events.
> 12. Audit any override of human-readable output markings.
> 13. Identify the date and time of the event, user, type of event, and
> success or failure of the event. For identification and authentication
> events, the origin of request (e.g., terminal ID) shall be included in the
> audit record. For events that introduce an object into a user's address
> space
> and for object deletion events, the audit record shall include the name of
> the object and the object's label.
> 14. The system administrator shall be able to selectively audit the
> actions
> of any one or more users based on individual identity and/or object label.
> 15. Raise alarms whenever a threshold is reached with respect to an
> auditing
> system resource (disk space in audit log volume) or when auditing has been
> turned off (either inadvertently or deliberately).
> 16. Provide details of databases/schemas/tables accessed and modified by
> the
> users and the administrator
> 17. Provide details of exceptional activity in certain columns (eg. Salary
> column incremented by more than 20%)
> With this background, we have the following questions:
> 1. Is it efficient to maintain auditing records in a separate audit
> table(s)
> or maintain it as part of the same table? What has been the collective
> experience of developers?
> 2. Is there a white paper on auditing in SQL Server 2005/.Net?
> 3. What is the native database support for auditing in SQL Server 2005?
> Would they address our concerns?
> 4. What are the readily available .Net components for auditing that we may
> use in this scenario?
>

Auditing DB Changes

It seems to me there are two common strategies for doing DB audit trails via a trigger:
1. On an update to a row, duplicate that row in another table withidentical rows, except for perhaps the extra columns which representchange date and changed by. Eg. When there is an update to the Customertable, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in acommon audit table record the table, row ID, field, previous value andnew value of the field.
I'm wondering about the pros and cons of each. More specifically, dothe pros and cons change if you are using an O/R mapper (I'm usingNHibernate.)
Some thoughts on method 1 . It seems nice for an O/R mapper, since youcould have, say, a CustomerAudit Object inherit from your Customerobject and just add the properties change date and changed by. Aproblem with this is you're going to add a whole lot of objects - onefor each object which you want to audit. Another drawback is that itcould be difficult to generate a history for a particular propertywhich was updated. Let's say I want to see the history of changes tothe customer's status. I have to load a collection of CustomerAuditobjects (which could be costly). Then I have to iterate through themand compare the status properties to generate a history of statuses.This is a pretty labor instensive method if you compare it to method 2,where the change is recorded by field, not row.
Some thoughts on method 2. It's nice since the changes are by field,not row, which (as above) makes generating a history easy. On the otherhand, you can never have a snapshot of a particular object at aparticular point in time. Moreover, I'm not sure how foreign keys wouldbe handled elegantly. I record that customer.statusID changes from 3 to6. I'd have to do a seperate join to the customerstatus table to getmeaning for 3 and 6 (which method 1 would do automatically).
Thoughts? Any preferred way to do this with an O/R mapper?
Thanks

nick7272:

It seems to me there are two common strategies for doing DB audit trails via a trigger:

1. On an update to a row, duplicate that row in another table with identical rows, except for perhaps the extra columns which represent change date and changed by. Eg. When there is an update to the Customer table, record the changes in Customer_Audit.

2. On an update to a row, check which fields were updated. Then in a common audit table record the table, row ID, field, previous value and new value of the field.
...

Nick7272 --

I am surprised there were no replies to your post.

You have the gist of the standard analysis above. Well done.

I want to pick up the topic because I am about to build yet another auditing system.

I think that there is at least one other option in addition to the ones you mention above. It is similar to similar to option 1. Note that in option 1, a developer must propogate schema changes in the tables-to-be-audited into the tables-doing-the-auditing. That can be a chore. I have implemented option 1 and it works OK for < 50 tables or so. More than that and it could be a problem if the schema changes. I am thinking that Option 3 (as yet never implemented by me) one could avoid the need for schema synchronization by using XML. Serialize an object to XML and store that. Sure, an audit recored for an object saved 1 year ago might have a different schema from an object saved 1 day ago, but all of the data would be there. I would be ugly; but, it would "mineable". It would tough to search; but, it would require no schema synchronizatoin. And so on. The problem would come from the complexity of the objects being serialized. Maybe the object could have a custom SerializeEx method that would be a lightweight version of the object, with just the necessary field names and data. Maybe. Anyway, I just wanted to mention this.

Regarding your post, I am wondering...

Since you made the post, have you discovered anything new?

What did you choose for a solution?

Please advise.

Thank you.

-- Mark Kamoski

Auditing changes to Scheduled Jobs

Hello,

Is there a way that I can create an audit trail of changes that are made to scheduled jobs in SQL Server 2000?

Thanks

This question is not directly related to SSIS, so I would try posting in the SQL Server Tools General forum to get more responses.

Bob

Auditing changes to fields in SQL2000

Is it possible to audit all changes to data within tables on SQL 2000? Not
concerned about auditing reads, only changes and deletions (a before/after
snapshot).
Wondering what performance impact should be expected, how to enable the
audit, and how to interpret the audit data?
Regards
RickYou can audit inserts, updates and deletes by adding the appropriate
triggers to the target tables, whereby the triggers write to audit tables.
You can capture the "before" image of each affected row. The after image
would be the current row in the audited table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Rick Bell" <rick@.ramkey.com.au> wrote in message
news:uCvgRdaaEHA.2016@.TK2MSFTNGP09.phx.gbl...
Is it possible to audit all changes to data within tables on SQL 2000? Not
concerned about auditing reads, only changes and deletions (a before/after
snapshot).
Wondering what performance impact should be expected, how to enable the
audit, and how to interpret the audit data?
Regards
Rick

Auditing changes to data

Hi Experts,
I want to audit the changes made to the data in the SQL
Server tables. How can I implement a audit mechanism?
Thanks,
Hari
Hi,
You will have to use either of below 2 options:-
1. Write Insert / Update and delete triggers on each tables
2. Enable profiler, You have to run the profiler all the time.
But I feel that your pupose can only be solved by writing custom triggers on
each tables.
Thanks
Hari
MCDBA
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari
|||Hari
Create Audit Table with the same structure as real table . Create an
UPDATE/DELETE/INSERT trigger on real table and insert any changes into audit
table.
create trigger trigger_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari
|||There are also third party tools which audit by reading the transaction
logs... Lumigent has such a tool (www.Lumigent.com.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari

Auditing changes to data

Hi Experts,
I want to audit the changes made to the data in the SQL
Server tables. How can I implement a audit mechanism?
Thanks,
HariHi,
You will have to use either of below 2 options:-
1. Write Insert / Update and delete triggers on each tables
2. Enable profiler, You have to run the profiler all the time.
But I feel that your pupose can only be solved by writing custom triggers on
each tables.
Thanks
Hari
MCDBA
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari|||Hari
Create Audit Table with the same structure as real table . Create an
UPDATE/DELETE/INSERT trigger on real table and insert any changes into audit
table.
create trigger trigger_MyTable on MyTable after update
as
if @.@.ROWCOUNT = 0
return
insert MyAuditTable
select
i.ID
, d.MyColumn
, i.MyColumn
from
inserted i
join
deleted d on d.ID = o.Id
go
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari|||There are also third party tools which audit by reading the transaction
logs... Lumigent has such a tool (www.Lumigent.com.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:278e01c47dd7$09469dc0$a401280a@.phx.gbl...
> Hi Experts,
> I want to audit the changes made to the data in the SQL
> Server tables. How can I implement a audit mechanism?
> Thanks,
> Hari