Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Using SQL Trace to Audit Database Access Expand / Collapse
Author
Message
Posted Thursday, December 12, 2002 12:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/trace.asp


Post #8774
Posted Thursday, December 19, 2002 1:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, 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





Post #49440
Posted Thursday, December 19, 2002 4:04 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Thursday, December 18, 2014 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #49441
Posted Tuesday, January 14, 2003 1:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2003 12:00 AM
Points: 31, 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)


Post #49442
Posted Tuesday, January 14, 2003 9:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, 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




Post #49443
Posted Friday, January 17, 2003 5:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 14, 2004 4:50 AM
Points: 37, 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?




Post #49444
Posted Friday, January 17, 2003 4:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, 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





Post #49445
Posted Monday, January 20, 2003 2:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 14, 2004 4:50 AM
Points: 37, 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.





Post #49446
Posted Monday, September 22, 2003 5:34 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:25 PM
Points: 564, Visits: 352
Haitong, how to make your script to do the trace automatically whenever the server is reboot ?




Post #49447
Posted Tuesday, September 23, 2003 8:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, 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



Post #49448
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse