Audit in SQL Server

Hi,
I need to setup a job in SQL Server for auditing all task made by the
sysadmin Group all time
I have tried to use the system stored procedures: sp_trace_create,
sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
any file or table.
I used this stored procedures in this way:
declare @.trace_id int
declare @.intOn bit
set @.intOn=1
exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
@.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
select @.trace_id
exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
@.on=@.intOn
exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
How can I make this task
Help me please
I know the SQL Profiler but I don't know the way to programming an automatic
task to run all time.
Thank so muchYou may want to look at a third party product such as Lumigent.
"Auditing SQL Server" wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much|||Auditing SQL Server wrote:
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's
> result in any file or table.
>
The best way to see the code used to create a trace is to create the
exact trace you want in Profiler (including the server manages trace
option). Make sure yo include only the events you want and the columns
you require. Set any necessary filter conditions.
Then use the File - Script Trace menu option to generate the T-SQL for
the trace.
You'll need to manually turn it into a job and also manage the stopping
of the trace (which is not scripted). Also, consider placing the trace
file somewhere else besides to root folder of the boot drive. Place it
on a disk with sufficient available space (like the log file/temdb
drive). You cannot view the collected trace data in the file until you
stop the trace, so you might want to have the job start a new trace
using an incrementing file ID (or use a the new_id() function for the
name) and then stop the old trace. Then you can view the trace data
using fn_trace_gettable or using Profiler (SP4 Profiler will require a
soon-to-be released request-only hotfix to view the data in the file).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Your problem seems to be @.options = 3. That is not documented. I tried with 2 and I got a proper
trace id back. Why don't you define the trace in Profiler and script that definition?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Auditing SQL Server" <Auditing SQL Server@.discussions.microsoft.com> wrote in message
news:84B4D6A4-045D-4442-ACB0-0332B84AEB63@.microsoft.com...
> Hi,
> I need to setup a job in SQL Server for auditing all task made by the
> sysadmin Group all time
> I have tried to use the system stored procedures: sp_trace_create,
> sp_trace_setevent, sp_trace_setstatus but I do not see the job's result in
> any file or table.
> I used this stored procedures in this way:
> declare @.trace_id int
> declare @.intOn bit
> set @.intOn=1
> exec sp_trace_create @.traceid = @.trace_id OUTPUT, @.options=3,
> @.tracefile=N'c:\Audit', @.maxfilesize = Null, @.stoptime = Null
> select @.trace_id
> exec sp_trace_setevent @.traceid = @.trace_id, @.eventid = 117, @.columnid=1,
> @.on=@.intOn
> exec sp_trace_setstatus @.traceid = @.trace_id, @.status = 1
> How can I make this task
> Help me please
> I know the SQL Profiler but I don't know the way to programming an automatic
> task to run all time.
> Thank so much