January 14, 2009 at 4:14 am
Hi,
Can any body help me? I need a Tsql which gives the details of Login and Logout of MSSQL2000 users other than running trace for it or enabling C2 configuration option.
I need the same to review for how much time and how many times the particular user has been logging in/out on SQL server.
January 14, 2009 at 4:40 am
Try sp_trace_create and sp_trace_setstatus, this might sort out your problem. Please refer to BOL for syntax and usage.
-Vikas Bindra
January 14, 2009 at 4:45 am
sp_who & sp_who2 give info about users currently logged in.
For getting the info about how many times a user logs in/out you will need a trace.
"Keep Trying"
January 14, 2009 at 10:54 am
thanks for reply.
Anything else without creating trace. I don't want to use trace.
January 14, 2009 at 11:33 pm
None that I am aware of.
-Vikas Bindra
January 15, 2009 at 11:38 pm
You can use the set of tables below and schedule a stored procedure every minute that takes a snapshot of the currently logged on users. This then captures start and end of sessions at a 1 minute level of granularity. I involved a table of specific users (av_users) because I was not alone in that SQL Server instance.
create table avx_user_log(
rn varchar(8),
computer varchar(15),
login datetime,
logout datetime)
go
create table avx_user_open_sessions(
rn varchar(8),
computer varchar(15),
login datetime)
go
create table avx_user_session_snapshot(
rn varchar(8),
computer varchar(15),
login datetime)
go
-- take a new snapshot
truncate table avx_user_sessions_snapshot
go
insert into avx_user_sessions_snapshot
select left(loginame,8),hostname,min(login_time)
from master.dbo.sysprocesses,av_users
where loginame collate Latin1_general_BIN = rn and db_name(dbid)='VIEWS'
group by loginame,hostname
go
-- transfer/close sessions to avx_user_log that have gone away
insert into avx_user_log
select rn,computer,login,getdate()
from avx_user_open_sessions
where rn+computer not in
(select rn+computer from avx_user_sessions_snapshot)
go
-- remove sessions that have gone away
delete from avx_user_open_sessions
where rn+computer not in
(select rn+computer from avx_user_sessions_snapshot)
go
-- remove sessions from current snapshot that were already in the list of open sessions
delete from avx_user_sessions_snapshot
where rn+computer in
(select rn+computer from avx_user_open_sessions)
go
-- transfer new sessions from snapshot to list of open sessions
insert into avx_user_open_sessions
select * from avx_user_sessions_snapshot
go
January 20, 2009 at 6:55 am
Thanks for reply.
It is useful for me at some extent. If you have any more idea regarding this please share with me.
January 20, 2009 at 9:18 pm
hemant789 (1/20/2009)
Thanks for reply.It is useful for me at some extent.
It's just a "hand made" trace.
Less effective and less accurate. If a connection was open and closed within the same minute you're never gonna notice.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply