audit trails

HI all,
Where can I read up about the best practice when it comes to creatin audit
trail. What to store how much to store, etc
What do some of the gurus do,
Thanks
RobertRobert Bravery wrote:
> HI all,
> Where can I read up about the best practice when it comes to creatin audit
> trail. What to store how much to store, etc
> What do some of the gurus do,
> Thanks
> Robert
Some examples here:
http://www.aspfaq.com/show.asp?id=2448
Good practice for audit (and for almost any application for that
matter) is to deny users direct access to tables. Otherwise there are
always likely to be ways to subvert your audit trail.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139305231.327268.266710@.f14g2000cwb.googlegroups.com...
> Robert Bravery wrote:
audit
> Some examples here:
> http://www.aspfaq.com/show.asp?id=2448
> Good practice for audit (and for almost any application for that
> matter) is to deny users direct access to tables. Otherwise there are
> always likely to be ways to subvert your audit trail.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Triggers can be used to track insert/update/deletes. However, for auditing
most any type of event (such as SQL selects, SP execution, altering objects,
etc.) you can use SQL Server Profiler:
http://msdn2.microsoft.com/ms190378.aspx
http://msdn2.microsoft.com/ms191148.aspx
http://msdn2.microsoft.com/ms190176.aspx
"Robert Bravery" <me@.u.com> wrote in message
news:%239iyBf8KGHA.1132@.TK2MSFTNGP10.phx.gbl...
> HI all,
> Where can I read up about the best practice when it comes to creatin audit
> trail. What to store how much to store, etc
> What do some of the gurus do,
> Thanks
> Robert
>
>
>

Audit Table

Hi all gurus
I want to create a store procedure which will generate an audit table and
track all the changes done in any of my tables in any database of my
server.
Please guide me where to start
Any guidance will be highly appreciated
ThanksYou probably won;t want a stored proc, but a trigger on each of the tables
you want to audit.. I believe there's a MS design pattern you can copy foor
this. Check out MSDN... In general,
1) decide what you want to record.. . some ideas are
Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
ChangeDatetime, Operation(Insert/Update/Delete), etc...
2) build a table to store the data
3) write trigger in each table that inserts new record in audit table for
each row being modified...
hth,
Charles
"AM" wrote:
> Hi all gurus
> I want to create a store procedure which will generate an audit table and
> track all the changes done in any of my tables in any database of my
> server.
> Please guide me where to start
> Any guidance will be highly appreciated
> Thanks
>
>|||hi,
You can add to the strategic or central production's tables fields such as:
-LastDML ('u': update, 'i': insert)
-LastDMLuser (linked to tables as tblsysusers and tblsysrights)
"CBretana" wrote:
> You probably won;t want a stored proc, but a trigger on each of the tables
> you want to audit.. I believe there's a MS design pattern you can copy foor
> this. Check out MSDN... In general,
> 1) decide what you want to record.. . some ideas are
> Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> ChangeDatetime, Operation(Insert/Update/Delete), etc...
> 2) build a table to store the data
> 3) write trigger in each table that inserts new record in audit table for
> each row being modified...
> hth,
> Charles
> "AM" wrote:
> > Hi all gurus
> >
> > I want to create a store procedure which will generate an audit table and
> > track all the changes done in any of my tables in any database of my
> > server.
> >
> > Please guide me where to start
> >
> > Any guidance will be highly appreciated
> >
> > Thanks
> >
> >
> >|||Hi all
I cannot change anykind on tables , so I can not add triggers on the tables
Thanks
AM
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> hi,
> You can add to the strategic or central production's tables fields such
as:
> -LastDML ('u': update, 'i': insert)
> -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
>
> "CBretana" wrote:
> > You probably won;t want a stored proc, but a trigger on each of the
tables
> > you want to audit.. I believe there's a MS design pattern you can copy
foor
> > this. Check out MSDN... In general,
> > 1) decide what you want to record.. . some ideas are
> > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> >
> > 2) build a table to store the data
> > 3) write trigger in each table that inserts new record in audit table
for
> > each row being modified...
> >
> > hth,
> > Charles
> >
> > "AM" wrote:
> >
> > > Hi all gurus
> > >
> > > I want to create a store procedure which will generate an audit table
and
> > > track all the changes done in any of my tables in any database of my
> > > server.
> > >
> > > Please guide me where to start
> > >
> > > Any guidance will be highly appreciated
> > >
> > > Thanks
> > >
> > >
> > >|||Then you will need to intercept the Stored Proc(s) that are inserting,
updating, or deleting record sfrom these tables and put the same code, more
or less in there, understanding that any changes made to the tables
directly, or from another as yet unwritten SP< will not be audited...
"AM" wrote:
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > hi,
> > You can add to the strategic or central production's tables fields such
> as:
> >
> > -LastDML ('u': update, 'i': insert)
> > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> >
> >
> > "CBretana" wrote:
> >
> > > You probably won;t want a stored proc, but a trigger on each of the
> tables
> > > you want to audit.. I believe there's a MS design pattern you can copy
> foor
> > > this. Check out MSDN... In general,
> > > 1) decide what you want to record.. . some ideas are
> > > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > >
> > > 2) build a table to store the data
> > > 3) write trigger in each table that inserts new record in audit table
> for
> > > each row being modified...
> > >
> > > hth,
> > > Charles
> > >
> > > "AM" wrote:
> > >
> > > > Hi all gurus
> > > >
> > > > I want to create a store procedure which will generate an audit table
> and
> > > > track all the changes done in any of my tables in any database of my
> > > > server.
> > > >
> > > > Please guide me where to start
> > > >
> > > > Any guidance will be highly appreciated
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
>
>|||This may be an option.
http://www.lumigent.com/
"AM" <anonymous@.extraquest.com> wrote in message
news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi all
> I cannot change anykind on tables , so I can not add triggers on the
tables
> Thanks
> AM
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > hi,
> > You can add to the strategic or central production's tables fields such
> as:
> >
> > -LastDML ('u': update, 'i': insert)
> > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> >
> >
> > "CBretana" wrote:
> >
> > > You probably won;t want a stored proc, but a trigger on each of the
> tables
> > > you want to audit.. I believe there's a MS design pattern you can
copy
> foor
> > > this. Check out MSDN... In general,
> > > 1) decide what you want to record.. . some ideas are
> > > Table, RecordIdentifier, Column, OldValue, NewValue, WHoChangedit,
> > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > >
> > > 2) build a table to store the data
> > > 3) write trigger in each table that inserts new record in audit table
> for
> > > each row being modified...
> > >
> > > hth,
> > > Charles
> > >
> > > "AM" wrote:
> > >
> > > > Hi all gurus
> > > >
> > > > I want to create a store procedure which will generate an audit
table
> and
> > > > track all the changes done in any of my tables in any database of
my
> > > > server.
> > > >
> > > > Please guide me where to start
> > > >
> > > > Any guidance will be highly appreciated
> > > >
> > > > Thanks
> > > >
> > > >
> > > >
>|||Thanks
"Terri" <terri@.cybernets.com> wrote in message
news:d2s32a$tkr$1@.reader2.nmix.net...
> This may be an option.
> http://www.lumigent.com/
>
> "AM" <anonymous@.extraquest.com> wrote in message
> news:ufPXUHUOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > Hi all
> >
> > I cannot change anykind on tables , so I can not add triggers on the
> tables
> >
> > Thanks
> > AM
> > "Enric" <Enric@.discussions.microsoft.com> wrote in message
> > news:D4198061-8BD2-47D5-B2F2-6D117618D84A@.microsoft.com...
> > > hi,
> > > You can add to the strategic or central production's tables fields
such
> > as:
> > >
> > > -LastDML ('u': update, 'i': insert)
> > > -LastDMLuser (linked to tables as tblsysusers and tblsysrights)
> > >
> > >
> > > "CBretana" wrote:
> > >
> > > > You probably won;t want a stored proc, but a trigger on each of the
> > tables
> > > > you want to audit.. I believe there's a MS design pattern you can
> copy
> > foor
> > > > this. Check out MSDN... In general,
> > > > 1) decide what you want to record.. . some ideas are
> > > > Table, RecordIdentifier, Column, OldValue, NewValue,
WHoChangedit,
> > > > ChangeDatetime, Operation(Insert/Update/Delete), etc...
> > > >
> > > > 2) build a table to store the data
> > > > 3) write trigger in each table that inserts new record in audit
table
> > for
> > > > each row being modified...
> > > >
> > > > hth,
> > > > Charles
> > > >
> > > > "AM" wrote:
> > > >
> > > > > Hi all gurus
> > > > >
> > > > > I want to create a store procedure which will generate an audit
> table
> > and
> > > > > track all the changes done in any of my tables in any database of
> my
> > > > > server.
> > > > >
> > > > > Please guide me where to start
> > > > >
> > > > > Any guidance will be highly appreciated
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > >
> >
> >
>