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

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.83 (35)

You rated this post out of 5. Change rating

Related content

External Article

SQL Server Audit: Magic without a Wizard

In SQL Server 2008, Microsoft introduced SQL Server Audit. This is much better than anything we had before, and is likely to meet the needs of all but the largest, or most highly-regulated industries. SQL Server 2008 Enterprise Edition includes all of the features, whereas SQL Server 2008 Standard Edition only provides a subset. What is most attractive about it, is that it is easy to administer, as Thomas LaRock explains.

2009-05-12

2,638 reads