SQL Server Audit Trace - things you need to take care!
Audit trace is very helpful when you want to monitor a particular processes on your SQL Server, even for the successful logins and recent users activity on the databsaes. But the default trace would have many columns with the information and you need to know which column and table you need to get the relevant information.
There is a difference between SQL Server 2000 and 2005 version, as within 2005 you could easily take help of DMVs such as sys.dm_exec_sessions as follows:
select Login_name, *from sys.dm_exec_sessions order by login_time desc
Where you can pipe the output to a file for a period of time where audit trace of using Profiler or server side trace is not required. Also within SQL 2005 you can try implementing Audit by using DDL triggers methods. From Service Pack 2 onwards you could allow these login triggers that can fire a T-SQL or stored procedure in response to a LOGON event. You can use a login trigger to audit and control users by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for specific logins. Bear in mind this will come into affect only when that login is authenticated on SQL Server where the user session is initiated and you can see the information under SQL Server error log. In any case if the authencitation is failed then you wouldn't see any information that means the failed logins information you can see but the trigger is not initiated.
Also you can use following code that is referred in SQL BOL:
ALTER TRIGGER Ops_Login
ON ALL SERVER
AFTER LOGIN
AS
PRINT SUSER_SNAME() + ' has just logged in to ' + LTRIM(@@ServerName) +
' SQL Server at '+LTRIM(getdate())
GO
IN addition to that to see what trigger is set on server level use:
SELECT * FROM sys.server_triggers;
In case if your enterprise is a financial based then you should have a permament audit system in place as per SOX guidelines, this is where such
audit login triggers would help you.
Also there are few TSQL statements you can run to see all the trace, file location and get trace data:
select * from ::fn_trace_getinfo(default)
go
select * from ::fn_trace_getinfo(default) where Property=2
go
SELECT * FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default) where starttime>'2007-10-15'
go
Bear in mind all the trace information on log.trc file would consist from the last restart of SQL Server only. On a periodic basis you should check these log files to ensure the disk space is not vanished due to a high activity on SQL Server that will have all kinds of information stored on the log files where the SQL binaries are stored such as C: drive. Also using few other TSQL statements you can disable this trace:
SELECT * FROM sys.traces WHERE path LIKE N'%audittrace_%'
To stop the trace
EXEC sp_trace_setstatus @traceid = <traceid>, @status = 0
To delete the trace definition from the server
EXEC sp_trace_setstatus @traceid = <traceid>, @status = 2
Also if you have enabled C2 audit mode on SQL Server then disable it and more information from this link.