Wednesday, February 25, 2009

Who has accessed my SQL Server

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(T.path, 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

No comments:

Post a Comment