Recently , I had to use osql for some remote auditing which I found useful. For those who don't know ,osql is a standard app that comes with SQL Server 2000 (T-SQL statements, system procedures, and script files can be executed). I had to audit some login activity for a suspected security breach. Essentially I was analysing attempted and failed login attempts. To configure this auditing, open Enterprise Manager, select a database server, right-click Properties, go to the Security tab, and set the required audit level. I recommend that you don't audit to the Application log; otherwise, SQL Server writes audit information about user-login activity to two places simultaneously and unnecessarily degrade system performance. After changing audit settings, restart the database.

Use the osql command line to enable auditing. You can use this tool to enable auditing on remote SQL Server systems. If you used an account with DBA privileges on the target database server to log on to Windows, type: osql -S -E

-S tells osql.exe to connect to a remote server and myservername is the name of the database server or instance. The -E option tells osql.exe to use the credentials with which you logged on to Windows to establish a trusted database connection. If you didn't use an account with DBA privileges, you need to type: osql -S
-U
-P

-U tells osql.exe that the following argument is the username (DBA privileges required) and -P signals that the next argument is the password for the specified account.

After enabling auditing for the default database or for an instance, the database server logs all activity to the data directory Jack Vamvas specified during the installation process.A disadvantage of using this method for audit log information is that it captures everything. To achieve a granular approach to this types of auditing, you'll need to turn to Server-Side Traces.

SQL Server writes all auditable activity to a file with the format audittrace_YYYYMMDDHHMMSS.trc, where YYYYMMDDHHMMSS is the log's creation time by year, month, day, hour, minute, and second

You can use SQL Server Profiler to view the log files that SQL Server creates.

Republished from http://www.sqlserver-dba.com.