SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SQL Trace to Audit Database Access


Using SQL Trace to Audit Database Access

Author
Message
Haidong Ji
Haidong Ji
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/trace.asp



Haidong Ji
Haidong Ji
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 60
Hi,

There is one mistake in the beginning of the scripts. Instead of DROP PROCEDURE usp_Login_Audit_Trace, the article says DROP PROCEDURE dbo.spRoleMembers. My apologies and much thanks to reader William Nabers. Got to be careful with "cut and paste"....
The correct script is at the end of this post.

In addition, there might be some compilation errors when you copy and paste the script to Query Analyzer. I think that's due to changes to formatting during the upload of the article to the web server. You might need to make some minor adjustment in order for it to work.

Thanks.

Haidong

 --Use the master database 

USE master

go

IF OBJECT_ID('dbo.usp_Login_Audit_Trace') IS NOT NULL

DROP PROCEDURE dbo.usp_Login_Audit_Trace

GO

CREATE proc usp_Login_Audit_Trace

@path nvarchar(128),

@duration smallint

as

/*

Author: Haidong Ji

Date: 11/08/2002

Purpose: Login audit. Try to track which logins and which databases are accessed

Set up trace to a file in the path specified in the @path parameter (use UNC path for network drive) for the number of days/hours/minutes specified in the @duration input parameter.

*/

declare @tracestarttime datetime

declare @traceident int

declare @options int

declare @filename nvarchar(245)

declare @filesize bigint

declare @tracestoptime datetime

declare @createcode int

declare @on bit

declare @startcode int



set @tracestarttime = current_timestamp

/* Set the name of the trace file. */

set @filename = cast(month(current_timestamp) as varchar) + '_' +

cast(day(current_timestamp) as varchar) + '_' +

cast(year(current_timestamp) as varchar) + '_' +

cast(datepart(hh, current_timestamp) as varchar) + '_' +

cast(datepart(mi, current_timestamp) as varchar) + '_' +

cast(datepart(ss, current_timestamp) as varchar)

set @options = 2

set @filename = @path + N'\' + @filename

set @filesize = 20

/* You can change the first parameter in the dateadd function to set how long your trace will be

For example, if it is hh, the trace will last @duration hours */

set @tracestoptime = dateadd(dd, @duration, @tracestarttime)

set @on = 1

--set up the trace

exec @createcode = sp_trace_create @traceid = @traceident output, @options = @options,

@tracefile = @filename, @maxfilesize = @filesize, @stoptime = @tracestoptime

if @createcode = 0

--trace created

begin

--set events and columns

--Trace Login event

exec sp_trace_setevent @traceident, 14, 1, @on

exec sp_trace_setevent @traceident, 14, 6, @on

exec sp_trace_setevent @traceident, 14, 7, @on

exec sp_trace_setevent @traceident, 14, 8, @on

exec sp_trace_setevent @traceident, 14, 9, @on

exec sp_trace_setevent @traceident, 14, 10, @on

exec sp_trace_setevent @traceident, 14, 11, @on

exec sp_trace_setevent @traceident, 14, 12, @on

exec sp_trace_setevent @traceident, 14, 14, @on

exec sp_trace_setevent @traceident, 14, 18, @on

exec sp_trace_setevent @traceident, 14, 34, @on

exec sp_trace_setevent @traceident, 14, 35, @on





--Trace Logout event

exec sp_trace_setevent @traceident, 15, 1, @on

exec sp_trace_setevent @traceident, 15, 6, @on

exec sp_trace_setevent @traceident, 15, 7, @on

exec sp_trace_setevent @traceident, 15, 8, @on

exec sp_trace_setevent @traceident, 15, 9, @on

exec sp_trace_setevent @traceident, 15, 10, @on

exec sp_trace_setevent @traceident, 15, 11, @on

exec sp_trace_setevent @traceident, 15, 12, @on

exec sp_trace_setevent @traceident, 15, 14, @on

exec sp_trace_setevent @traceident, 15, 18, @on

exec sp_trace_setevent @traceident, 15, 34, @on

exec sp_trace_setevent @traceident, 15, 35, @on

--Trace Audit Object GDR event

exec sp_trace_setevent @traceident, 103, 1, @on

exec sp_trace_setevent @traceident, 103, 6, @on

exec sp_trace_setevent @traceident, 103, 7, @on

exec sp_trace_setevent @traceident, 103, 8, @on

exec sp_trace_setevent @traceident, 103, 9, @on

exec sp_trace_setevent @traceident, 103, 10, @on

exec sp_trace_setevent @traceident, 103, 11, @on

exec sp_trace_setevent @traceident, 103, 12, @on

exec sp_trace_setevent @traceident, 103, 14, @on

exec sp_trace_setevent @traceident, 103, 18, @on

exec sp_trace_setevent @traceident, 103, 34, @on

exec sp_trace_setevent @traceident, 103, 35, @on

--Trace Audit Add/Drop Login event

exec sp_trace_setevent @traceident, 104, 1, @on

exec sp_trace_setevent @traceident, 104, 6, @on

exec sp_trace_setevent @traceident, 104, 7, @on

exec sp_trace_setevent @traceident, 104, 8, @on

exec sp_trace_setevent @traceident, 104, 9, @on

exec sp_trace_setevent @traceident, 104, 10, @on

exec sp_trace_setevent @traceident, 104, 11, @on

exec sp_trace_setevent @traceident, 104, 12, @on

exec sp_trace_setevent @traceident, 104, 14, @on

exec sp_trace_setevent @traceident, 104, 18, @on

exec sp_trace_setevent @traceident, 104, 34, @on

exec sp_trace_setevent @traceident, 104, 35, @on

--Trace Audit Login GDR event

exec sp_trace_setevent @traceident, 105, 1, @on

exec sp_trace_setevent @traceident, 105, 6, @on

exec sp_trace_setevent @traceident, 105, 7, @on

exec sp_trace_setevent @traceident, 105, 8, @on

exec sp_trace_setevent @traceident, 105, 9, @on

exec sp_trace_setevent @traceident, 105, 10, @on

exec sp_trace_setevent @traceident, 105, 11, @on

exec sp_trace_setevent @traceident, 105, 12, @on

exec sp_trace_setevent @traceident, 105, 14, @on

exec sp_trace_setevent @traceident, 105, 18, @on

exec sp_trace_setevent @traceident, 105, 34, @on

exec sp_trace_setevent @traceident, 105, 35, @on

--Trace Audit Login Change Property event

exec sp_trace_setevent @traceident, 106, 1, @on

exec sp_trace_setevent @traceident, 106, 6, @on

exec sp_trace_setevent @traceident, 106, 7, @on

exec sp_trace_setevent @traceident, 106, 8, @on

exec sp_trace_setevent @traceident, 106, 9, @on

exec sp_trace_setevent @traceident, 106, 10, @on

exec sp_trace_setevent @traceident, 106, 11, @on

exec sp_trace_setevent @traceident, 106, 12, @on

exec sp_trace_setevent @traceident, 106, 14, @on

exec sp_trace_setevent @traceident, 106, 18, @on

exec sp_trace_setevent @traceident, 106, 34, @on

exec sp_trace_setevent @traceident, 106, 35, @on


--Trace Audit Add Login to Server Role event

exec sp_trace_setevent @traceident, 108, 1, @on

exec sp_trace_setevent @traceident, 108, 6, @on

exec sp_trace_setevent @traceident, 108, 7, @on

exec sp_trace_setevent @traceident, 108, 8, @on

exec sp_trace_setevent @traceident, 108, 9, @on

exec sp_trace_setevent @traceident, 108, 10, @on

exec sp_trace_setevent @traceident, 108, 11, @on

exec sp_trace_setevent @traceident, 108, 12, @on

exec sp_trace_setevent @traceident, 108, 14, @on

exec sp_trace_setevent @traceident, 108, 18, @on

exec sp_trace_setevent @traceident, 108, 34, @on

exec sp_trace_setevent @traceident, 108, 35, @on

--Trace Audit Add DB User event

exec sp_trace_setevent @traceident, 109, 1, @on

exec sp_trace_setevent @traceident, 109, 6, @on

exec sp_trace_setevent @traceident, 109, 7, @on

exec sp_trace_setevent @traceident, 109, 8, @on

exec sp_trace_setevent @traceident, 109, 9, @on

exec sp_trace_setevent @traceident, 109, 10, @on

exec sp_trace_setevent @traceident, 109, 11, @on

exec sp_trace_setevent @traceident, 109, 12, @on

exec sp_trace_setevent @traceident, 109, 14, @on

exec sp_trace_setevent @traceident, 109, 18, @on

exec sp_trace_setevent @traceident, 109, 34, @on

exec sp_trace_setevent @traceident, 109, 35, @on

--Trace Audit Add Member to DB event

exec sp_trace_setevent @traceident, 110, 1, @on

exec sp_trace_setevent @traceident, 110, 6, @on

exec sp_trace_setevent @traceident, 110, 7, @on

exec sp_trace_setevent @traceident, 110, 8, @on

exec sp_trace_setevent @traceident, 110, 9, @on

exec sp_trace_setevent @traceident, 110, 10, @on

exec sp_trace_setevent @traceident, 110, 11, @on

exec sp_trace_setevent @traceident, 110, 12, @on

exec sp_trace_setevent @traceident, 110, 14, @on

exec sp_trace_setevent @traceident, 110, 18, @on

exec sp_trace_setevent @traceident, 110, 34, @on

exec sp_trace_setevent @traceident, 110, 35, @on

--Trace Audit Add/Drop Role event

exec sp_trace_setevent @traceident, 111, 1, @on

exec sp_trace_setevent @traceident, 111, 6, @on

exec sp_trace_setevent @traceident, 111, 7, @on

exec sp_trace_setevent @traceident, 111, 8, @on

exec sp_trace_setevent @traceident, 111, 9, @on

exec sp_trace_setevent @traceident, 111, 10, @on

exec sp_trace_setevent @traceident, 111, 11, @on

exec sp_trace_setevent @traceident, 111, 12, @on

exec sp_trace_setevent @traceident, 111, 14, @on

exec sp_trace_setevent @traceident, 111, 18, @on

exec sp_trace_setevent @traceident, 111, 34, @on

exec sp_trace_setevent @traceident, 111, 35, @on

--filter Profiler

exec sp_trace_setfilter @traceid = @traceident, @columid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'SQL Profiler'

--start the trace

exec @startcode = sp_trace_setstatus @traceid = @traceident, @status = 1

if @startcode = 0

begin

select 'Trace started at ' + cast(@tracestarttime as varchar) + ' for ' + cast(@duration as varchar)+

' minutes; trace id is ' + cast(@traceident as nvarchar) + '.'
end
else
begin
goto Error
end
end
else
begin
goto Error
end

return

Error:
select 'Error starting trace.'
return
GO




Andy Warren
Andy Warren
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Moderators
Points: 36978 Visits: 2762
Thanks for posting the correction!

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
deema
deema
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 1
I have tried to execute the listed stored procedure. the procedure does execute and the file is created but with no data in it (0 KB)



Haidong Ji
Haidong Ji
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 60
Deema,

Thanks for reading my article.

It's normal for the file size to be 0 when the trace just got started. SQL Trace does not write to this file right away. For a short trace (a couple of minutes), the size will be 0kb until the trace is over. For a long trace, the trace size will increase periodically.

To view all the running trace, use the following T-SQL:
SELECT * FROM :: fn_trace_getinfo(default)

To stop a trace, use the following T-SQL:
sp_trace_setstatus @traceId, 0

Hope this helps
Haidong
http://www.sqlservercentral.com/columnists/hji



shoayb
shoayb
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 1
hi, we've implemented this on our sql server and it's working a treat, however, I've become greedy and also want to get the objectid of the object that is being traced (in particular DTS Designer
and SQLAgent - Job Manager, i've tried adding the following line into the stored procedure for each Trace Event block...
exec sp_trace_setevent @traceident, XX, 22, @on
however nothing is returned. I'm not an sql guru so it may be something really fundamental that I'm not understanding, can you help?



Haidong Ji
Haidong Ji
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 60
shoayb,

I am glad you found this article helpful. In fact, you could easily edit that stored procedure and use it to trace deadlocks, do performence tuning and lots of other things.

>>exec sp_trace_setevent @traceident, XX, >>22, @on
>>however nothing is returned.

I am not too sure what you mean by "Nothing is returned"
1. If the ObjectID column is added to the trace output file but the data column is empty, then that is normal, because some columns are not applicable to certain events;

2. If the ObjectID column does not exist in the output file, that means this column was not added to the trace. There are 2 ways to resolve this:
a. You could add this column on the fly:
1. Use SELECT * FROM :: fn_trace_getinfo(default) to find out your trace ID;
2. Use sp_trace_setstatus @traceId, 0. This will pause the trace but not kill it;
3. You can then issue exec sp_trace_setevent @traceid, XX, 22, @on to add this column;
4. After you are done, issue SP_trace_setstatus @traceId, 1 to restart the trace;

b. This is the permanent solution
1. Use SELECT * FROM :: fn_trace_getinfo(default) to find out your trace ID;
2. Use sp_trace_setstatus @traceId, 0. This will pause the trace but not kill it;
3. Use sp_trace_setstatus @traceId, 2. This will kill the trace; NOTE: YOU MUST USE STEP 2 AND 3 TO KILL AND DELETE A TRACE. SIMPLY USING sp_trace_setstatus @traceId, 2 WILL NOT WORK;
4. Edit the your stored procedure and add the data column there and you are good to go.

Hope this helps.

Haidong
http://www.sqlservercentral.com/columnists/hji



shoayb
shoayb
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 1
Hi Haidong,
You answered my question with...
"that is normal, because some columns are not applicable to certain events".
After I posted my question I found 2 very useul articles...
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=23016
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_6koi.asp
The second one details which columns are applicable for given events.
Thanks for your help and reply.



awu
awu
SSC Eights!
SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)SSC Eights! (985 reputation)

Group: General Forum Members
Points: 985 Visits: 415
Haitong, how to make your script to do the trace automatically whenever the server is reboot ?



Haidong Ji
Haidong Ji
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 60
awu,

Assuming SQLSERVERAGENT service starts automatically, you can create a job that runs this procedure. When schedule this job, under Schedule Type, pick the "Start automatically when SQL Server Agent starts" option.

Hope this helps.

Haidong



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search