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


Who has accessed my 2005 server?


Who has accessed my 2005 server?

Author
Message
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78942 Visits: 14971
Comments posted to this topic are about the item Who has accessed my 2005 server?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jane Matheson-154829
Jane Matheson-154829
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2206 Visits: 185
Really useful script that shows how to do use the trace. Thanks!
YSLGuru
YSLGuru
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8418 Visits: 1670
This probably sounds like a no-brainer but I have a question about the code. I understand what it does but what I didn't know about is the trace itself. Does SQL Server by default always run a trace on every connection and that's what this code is returning the details on? IO thought traces were restricted to Profiler. If the answer is yes then is this default trace similar to a trace in Profiler?

Thanks

Kindest Regards,

Just say No to Facebook!
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78942 Visits: 14971
SQL Server 2005 has a basic trace that is created when you install SQL Server and starts on start-up. You could remove this trace if you wanted to, but since you didn't know about it, it obviously isn't negatively affecting performance. This is also what many of the Management reports get their data from.

Profiler is actually just a GUI interface on top of server-side tracing. You could check out the Profiler video's on www.jumpstarttv.com where I explain how to use Profiler to generate a script for a server-side trace.

You can also lookup server-side trace in BOL.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
YSLGuru
YSLGuru
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8418 Visits: 1670
Jack -

Thanks for the inf. So the video you are referencing, it can show one how to basically create your own custom server side trace to do what the default one in 2005 does but do it capturing what you;ve specified in your custom trace? I imagine based on your descrpition that the video will show you how to take a trace you've defined (like a termplate trace you've created) and apply that as the server side trace.

Thanks

Kindest Regards,

Just say No to Facebook!
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78942 Visits: 14971
Yes, within reason. It does not demonstrate how to start the trace when SQL Server starts.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Vitali Lisau
Vitali Lisau
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 183
The code is nice, but it actually shows only the records from the last trace file (SQL Srv 2005 Ent , SP2 and SP3).

select * from sys.traces
- returns the last trace file, e.g
'E:\MSSQL\SystemData\MSSQL.1\MSSQL\LOG\log_734.trc'

But ::fn_trace_gettable() - requires the first file name from the sequence.

For example, if there are 5 files stored, then it is necessary to pass
('E:\MSSQL\SystemData\MSSQL.1\MSSQL\LOG\log_730.trc', 5)

Otherwise, you will see info only from the last file.

I also tried going back by passing the last file name and negative number of files(-3), but it did not work as desired - only the last log info was returned.
david.sturm
david.sturm
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 126
when I run the script( as posted), error is returned:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'T'.

this: select * from sys.traces
returns: C:\Program Files\Microsoft SQL Server\MSSQL\log\log_77.trc

I am a novice at Traces and profiler.
what might be my error?
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78942 Visits: 14971
Vitali Lisau (2/9/2009)
The code is nice, but it actually shows only the records from the last trace file (SQL Srv 2005 Ent , SP2 and SP3).

select * from sys.traces
- returns the last trace file, e.g
'E:\MSSQL\SystemData\MSSQL.1\MSSQL\LOG\log_734.trc'

But ::fn_trace_gettable() - requires the first file name from the sequence.

For example, if there are 5 files stored, then it is necessary to pass
('E:\MSSQL\SystemData\MSSQL.1\MSSQL\LOG\log_730.trc', 5)

Otherwise, you will see info only from the last file.

I also tried going back by passing the last file name and negative number of files(-3), but it did not work as desired - only the last log info was returned.


You are correct that is a nice catch. This actual happens because Path actually includes the file number instead of the default name. I will correct the code to reflect this.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78942 Visits: 14971
I modified the code in the script to fix this oversight. Steve just needs to release the updated code. Here it is here as well:


SELECT
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
Min(I.StartTime) as first_used,
Max(I.StartTime) as last_used,
S.principal_id,
S.sid,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(CASE
WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
ELSE T.[path]
End, T.max_files) I LEFT JOIN
sys.server_principals S ON
CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 And
I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name



I overlooked the fact that the Path stored in sys.traces includes the numeric extension to the file name. The CASE strips the numeric portion of the file name.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
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