Technical Article

Who has accessed my 2005 server?

,

This script returns login information from the default trace created in SQL Server 2005. When the sys.server_principals data is null that would mean the login is allowed via a Windows Group.

sys.traces provides the information for the default trace such as the file path and the max files.

fn_trace_gettable returns the data from trace file(s) in table format.

sys.server_principals is the way you should access server logins in SQL Server 2005, replacing syslogins.

An important thing to note is that the default trace will create up to 100MB (5 20MB files) of event data and then begin wrapping. Also it creates a new file when ever the SQL Server is restarted so you may not have the full 100MB of data if you reboot or restart SQL Server often.

Edit 02-09-2009:

Modified the code to strip the numerical portion of the trace path (Log_nn.trc) as this caused only the current file to be loaded.

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

Rate

4.83 (35)

You rated this post out of 5. Change rating

Share

Share

Rate

4.83 (35)

You rated this post out of 5. Change rating