October 7, 2008 at 12:28 pm
Hi Guys,
I have been looking for some query that gives me a list of users I have on my database, I found posted here the following:
select sp.last_batch, sp.loginame
from master..sysprocesses sp, master..sysdatabases sd
where sp.dbid=sd.dbid
...but how can I know which are ....which were the last time someone access to my database, is this the same as last_batch column?
Thank you.
October 7, 2008 at 12:51 pm
Yes, you last_batch column.
But sysprocesses keeps only currently connected users. If you want to trace a user which has disconnected already you will not find him here. In this case you need to creata an auditing by using database triggers.
October 7, 2008 at 1:03 pm
Since you are running 2005, if you have not disabled the default system trace, you can actually use the following query to look at the current information;
SELECT top 100 I.*
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
and I.TextData is not null
Add another criteria in the where clause to find the proper login name. I wouldn't consider this robust enough for a true auditing solution but for a quick glance it works pretty well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 7, 2008 at 1:14 pm
and how about to know when was the last time my database was accessed?, does this query give me that option also?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply