Who had accessed my SQL Server

• vamshi.sql

  SSCommitted

  Points: 1725

  More actions

  August 2, 2010 at 1:53 am

  #99345

  hi,

  I need to know the query : who had logon to my SQL Server and who accessed the databases ??

  I am able to get the results in SQL Server 2005 by using below Query but in SQL Server 2000 it gives error.So, please anyone help me out by providing the query which will works in SQL server 2000..

  Query [SQL Server 2005] :

  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

  Thanks in Advance,

  Vamshi.

• Adiga

  One Orange Chip

  Points: 27284

  More actions

  August 2, 2010 at 4:14 am

  #1200901

  In SQL Server 2000, there is no default trace running unlike SQL Server 2005 and above. The query you had posted gets the information from the default trace.

  In order to do the same in SQL Server 2000, you have to run a trace when SQL Server starts up and get the information.

  Pradeep Adiga
  Blog: sqldbadiaries.com
  Twitter: @pradeepadiga