Re: Auditing sql server at runtime

Well, using SQL Trace you can do the following:

1. Use SQL Profiler to define your trace, then script it out. (Because you state that you want to see what is being submitted, I would suggest starting with tracing only SQL batches, not individual stored procedures, statements, etc.)
2. Create a SQL Agent job to run that trace, paste the script into it. (You may need to do a couple of manual edits of the script, since file name (if you trace to file) and end time do not make it into the script.)
3. Create a stored procedure that will start the SQL Agent job.
4. User sp_procoption to mark the stored procedure for autoexecution when the SQL Server is restarted.

You can send the trace to a SQL Server table, but that adds extra load to the server. Or you can trace to files. The file size setting will limit how big a single file is, but the trace can roll over to numbered files. So, once a file is complete, you could import it to a table for review. (Or even import it to another server.)

SQL Profiler is just a user interface. Once you automate the trace, you may not need to use profiler again for this trace.

RLF



"Manpreet Singh" <ManpreetSingh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:13AEF04D-3BF2-49B6-96A8-2DA504C02ED3@xxxxxxxxxxxxxxxx
Is there a any way through which I can intersect the Sql server 2005 traffic?
I know I can use profiler or profiler trace to achieve this task, but the
problem is that, I wanted to do it programmatically and at run time. eg like
capturing 1433 port data and getting queries out of them etc . Please help me
as I wanted to create a application which captures and log all the queries
which are fired against sql server 2005

Manpreet Singh
http://crazysql.co.nr
http://crazysql.wordpress.com
MCITP – DB Admin, DB Dev SQL Server 2005
MCTS – MOSS 2007, SQL server 2005


.

Relevant Pages

  • Re: SQL 7 Traces
    ... Profiler 2K can generate the xp calls for a defined trace in 7 format. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Auditing sql server at runtime
    ... Like Linchi said, depending on what you trace, the load may not be too great. ... That is why I suggested tracing only SQL batches, since this is the level at which commands are submitted to the SQL Server. ... Use SQL Profiler to define your trace, ...
    (microsoft.public.sqlserver.clients)
  • Re: Wartungsfreier SQL-Server!?
    ... wenn man sonst keine Anhaltspunkte hat. ... Wie kann ich den "SQL Profiler Trace" mir anzeigen lassen? ...
    (microsoft.public.de.sqlserver)
  • Re: User and Login auditing
    ... I am running SQL 7. ... You could put triggers on your trace table. ... How to Trace in SQL Server by Using Extended Stored ...
    (microsoft.public.sqlserver.security)
  • Re: Tracking user activities
    ... >> I'd like these settings to persist permanently and the event records to ... > In SQL 2000, this is not very simple to do. ... > Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... select Script Trace. ...
    (comp.databases.ms-sqlserver)
Loading