How to log off idle users from SQL Server ?

I have been given the task of writing a script to log idle users off a SQL Server database. While there is a user defined table LogDetails which contains a field showing the time of logging in and another field giving the user's identity, I am not aware of any instruction in T-SQL which will do the necessary. Is it possible to do this using a script or is there another way unknown to the person making the request ?
Philip van GassDatabase Developer/AdministratorAsked:
Who is Participating?
sameer2010Connect With a Mentor Commented:
It would actually generate statements like KILL SPID1; KILL SPID2, etc. FOR XML is used to serialize the rows data to create a stream of KILL commands. STUFF would remove extra ; before dynamic SQL is executed,
Try this
declare @str varchar(1000)

;with t as(
select t1.login_name,t2.session_id from (
select login_name,max(last_request_end_time) last_request_end_time
from sys.dm_exec_sessions
where is_user_process=1
group by login_name) t1, sys.dm_exec_sessions t2
dateadd(MINUTE, 15, t1.last_request_end_time) < getdate()
and t1.last_request_end_time=t2.last_request_end_time
and t1.login_name=t2.login_name

select @str=stuff((select 'kill ' + cast(session_id as varchar(10)) + ';' from t for xml path('')),1,0,'') 

Open in new window

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
^^^ nice
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Thanks for this sameer2010. I have been busy testing it and will let you know how things turn out. I just need some clarification on the 'select' statement at the end which uses the 'STUFF' command. I understand what the 'STUFF' command does but not in this particular case as it looks like it is doing something strange. Also what is the purpose of the 'xml path' ?

Philip van GassDatabase Developer/AdministratorAuthor Commented:
I am accepting this as the solution although it has not been tested live yet.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.