How to create SQL Login audit log?

Hi, All
I use SQL Server 2000 and Win 2003 Server. I try to create a SQL Server
Login audit log using Profiler, but cannot find the tools. I looked at the
Windows Event Viewer, it only logs Windows authentications users for SQL
Server not SQL Server users (Mixed mode users).
Please help on creating SQL Server Login log (username,password and time).
Thanks
KaiHello,
SQL Profiler is a component of SQL Client Tools. Check if you have
installed SQL Client Tools on your server. By default, you can start SQL
Profiler by clicking Start > All Programs > Microsoft SQL Server >
Profiler.
To audit on successful login attempts or failed login attempts, you need to
select the option "Audit level: success" or "Audit level: failure" or
"Audit level: All" in the SQL Server Properties (Security Tab). You can
refer to the following information in the "SQL Server Properties (Security
Tab)" topic in SQL server Books Online(BOL):
All
Audit on both successful and failed login attempts. You can record
attempted user accesses as well as other SQL Server log information, and
enable auditing for both security modes, and you can record information on
both trusted and nontrusted connections. Log records for these events
appear in the Windows application log, the SQL Server error log, or both,
depending on how you configure logging for your SQL Server.
If you select this option, you must stop and restart the server to enable
auditing.
--
For more information, refer to the "SQL Server Properties (Security Tab)"
topic in BOL.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Guo,
Thanks for your help.
I setup "All" for Audit level in security, after that, in application
view, I restart SQL Server, I found user colum is "N/A" or "System". Any
thing I did not do?
Thanks
Kai
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:15x7bmQjFHA.588@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> SQL Profiler is a component of SQL Client Tools. Check if you have
> installed SQL Client Tools on your server. By default, you can start SQL
> Profiler by clicking Start > All Programs > Microsoft SQL Server >
> Profiler.
> To audit on successful login attempts or failed login attempts, you need
> to
> select the option "Audit level: success" or "Audit level: failure" or
> "Audit level: All" in the SQL Server Properties (Security Tab). You can
> refer to the following information in the "SQL Server Properties (Security
> Tab)" topic in SQL server Books Online(BOL):
> --
> All
> Audit on both successful and failed login attempts. You can record
> attempted user accesses as well as other SQL Server log information, and
> enable auditing for both security modes, and you can record information on
> both trusted and nontrusted connections. Log records for these events
> appear in the Windows application log, the SQL Server error log, or both,
> depending on how you configure logging for your SQL Server.
> If you select this option, you must stop and restart the server to enable
> auditing.
> --
> For more information, refer to the "SQL Server Properties (Security Tab)"
> topic in BOL.
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hello,
User colum is "N/A" in application view of Event Viewer if you log on as a
SQL login. However, double-click the event , you may read the information
looks like the followings in the event properties:
18454 :
Login succeeded for user 'sa'. Connection: Non-Trusted.
18456 :
Login failed for user 'sa'.
or you can find the following information in the SQL logs:
2005-07-20 16:48:10.32 logon Login failed for user 'sa'.
2005-07-20 16:48:14.75 logon Login succeeded for user 'sa'. Connection:
Non-Trusted.
By default, the SQL logs located in the folder "C:\Program Files\Microsoft
SQL Server\MSSQL\LOG".
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

How to create an Audit Table

Can anyone help, I am able to create a trigger that will populate a audit table everytime one of my tables columns data changes, but I have an applications from another user that has a stored proceudre and when that is called from an application it hit the original table twice, so the audit table will get a duplicate entry. How do you prevent an AUDIT TABLE from inserting a duplicate entry
Here is my trigger:
Create TRIGGER tg_audit_task_order_awardees on xxx
for INSERT,UPDATE,DELETE as

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)

Select
'OLD',
del.to_awardee,
del.solicitation_id,
del.contract_id,
del.order_number,
del.amount,
del.show_public,
getdate(),
del.modified_user
FROM deleted del

/* for a new record */

INSERT INTO audit_task_order_awardees(
audit_log_type,
to_awardee,
solicitation_id,
contract_id,
order_number,
amount,
show_public,
audit_changedatetime,
audit_user)
Select
'NEW',
ins.to_awardee,
ins.solicitation_id,
ins.contract_id,
ins.order_number,
ins.amount,
ins.show_public,
getdate(),
ins.modified_user
FROM inserted ins

Take a look at this article... offcourse there are other ways to do it, but this might give you some ideas to do it... I believe the only problem is it can't handle text and ntext fields:http://www.codeproject.com/database/AuditTriggers.asp