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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy