Audit

25 Aug 2005 5:08 PM

ReTF

Hi All,
I have one audit table in my db and i would like to do this:

1: always that user login in SQL I would like add one entry in this table.
(like this, USER 'xxx' SID 'xxx' log on Server 13:30:22...) I use Mixed
Mode (authentication)
2: always that user INSER, DELET or UPDATE tables, I would like add one
entry in audit table. (like this, USER 'xxx' SID 'xxx' DELET of table'xx'
13:30:22...)

Have any way to do this, or have other better way to do this?

Thanks

25 Aug 2005 5:10 PM

Aaron Bertrand [SQL Server MVP]

Third party tools are going to be much better than re-inventing the wheel.
Lumigent's Entegra comes to mind, but there are several others available.

Show quote

"ReTF" <re.tf@newsgroup.nospam> wrote in message
news:%23BmV0bZqFHA.3352@TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have one audit table in my db and i would like to do this:
>
> 1: always that user login in SQL I would like add one entry in this
> table. (like this, USER 'xxx' SID 'xxx' log on Server 13:30:22...) I use
> Mixed Mode (authentication)
> 2: always that user INSER, DELET or UPDATE tables, I would like add one
> entry in audit table. (like this, USER 'xxx' SID 'xxx' DELET of table'xx'
> 13:30:22...)
>
> Have any way to do this, or have other better way to do this?
>
> Thanks
>

25 Aug 2005 5:23 PM

B.M

I'm total agree with Aaron Bertrand, but if you can not afford such tools
then the only way in SQLServer 2000 is
- Profiler (not from GUI)
- c2 audit mode option (see BOL)

Fortunately SQLServer 2005 helps a lot with DDL triggers

Show quote

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:uMhz0eZqFHA.3920@TK2MSFTNGP09.phx.gbl...
> Third party tools are going to be much better than re-inventing the wheel.
> Lumigent's Entegra comes to mind, but there are several others available.
>
>
>
> "ReTF" <re.tf@newsgroup.nospam> wrote in message
> news:%23BmV0bZqFHA.3352@TK2MSFTNGP14.phx.gbl...
>> Hi All,
>> I have one audit table in my db and i would like to do this:
>>
>> 1: always that user login in SQL I would like add one entry in this
>> table. (like this, USER 'xxx' SID 'xxx' log on Server 13:30:22...) I use
>> Mixed Mode (authentication)
>> 2: always that user INSER, DELET or UPDATE tables, I would like add one
>> entry in audit table. (like this, USER 'xxx' SID 'xxx' DELET of table'xx'
>> 13:30:22...)
>>
>> Have any way to do this, or have other better way to do this?
>>
>> Thanks
>>
>
>