Who has accessed my 2005 server?

  • Comments posted to this topic are about the item Who has accessed my 2005 server?

  • Really useful script that shows how to do use the trace. Thanks!

  • 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!
  • 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 http://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.

    • This reply was modified 4 years, 11 months ago by  Jack Corbett.
    • This reply was modified 4 years, 6 months ago by  Dave Convery.
  • 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!
  • Yes, within reason. It does not demonstrate how to start the trace when SQL Server starts.

  • 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.

  • 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?

  • 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.

  • 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.

  • Great script Jack! Thanks for this and I know its been out for a while already but I have been real busy. This will help me a lot. I will definitely implement it. I would also like to get a list of login failures so I can follow it up because someone one day told me that if you have a lot of login failures it may be someone trying to hack the database. Be glad if you could help me!

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Thanks Manie.

    You can use the default trace to check for login failures, here's an idea on how:

    SELECT

    I.*

    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 JOIN

    sys.trace_events AS TE ON

    I.EventClass = TE.trace_event_id

    WHERE

    TE.[name] = 'Audit Login Failed'

    At R. Barry Young's suggestion I am working on a presentation about mining the default trace that will likely turn into at least one article here at SSC within a year or so. Your question helps with ideas. It takes me awhile to write and I have a series on Profiler that I need to finish first.

  • Thanks Jack, this is real nice and I have already added it to my collection of scripts. I look forward to that articles about the Profiler!

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Jack Corbett (2/9/2009)


    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,

    .....

    {Removed for brevity}

    ... 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.

    Apolagies if this sounds dumb because I may just be missing something but the way I read your reply with this new code was that it would load all of the traace files and not just the last one else why strip out the file number portion of the trace file name? When I run this exact code as is I only get data from the most recent trace file.

    Whats weird about this is I swear there was a forum posting discussing this same thing with the fn_Trace_Gettable() function/procedure and I thought someone did list a way to get the thing to load content from all trc files in that same directory.

    Am I going crazy or is that familiar to anyone else, that there is a way to load all the trc file?

    Thanks Jack!

    Kindest Regards,

    Just say No to Facebook!
  • The "new" code with the file number removed should load all the files for the specified trace. On my laptop, which I reboot somewhat regularly, and does not have a really active SQL Server, the code without removing the file number only queries the "active" file while the new code returns data from all the files.

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply