You don't have many options here if you're using SQL Server 2005. You can use Login Auditing at the server level, which will track server logins in the Event Viewer. You could also enable C2 audit tracing, but this can be cumbersome. You could possibly try to define DDL triggers on some system tables, but this is very risky. You're best bet is to upgrade to SQL 2008 if possible.
When you think about using AUDIT LOG then it may prove costly in terms of storage too, as you wanted to capture everything. As referred you can using logon triggers - http://aspadvice.com/blogs/andrewmo...SQL-Server-2005-Audit-Log-Using-Triggers.aspx to get information. But before that I would like to ask is this a compulsory to capture, or an occasional to get informaiton.
thanks to u for your replay This Database should be kept in higher secure manner,then we need to keep monitoring , u have a sugestion in case of occasional need the information
If there is no chance to upgrade SQL to 2008 then you may have to depend upon 3rd party tools on the AUDIT requirement for the environment. In terms of security it goes with other policies of access you should be aware take a look at blog posts on http://sqlserver-qa.net/blogs/perftune/archive/tags/security/default.aspx tags.
