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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sameer2010Commented:
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
where
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,'') 
exec(@str)

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
^^^ nice
0
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' ?

Thanks
SkyBluePhil
0
sameer2010Commented:
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,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Philip van GassDatabase Developer/AdministratorAuthor Commented:
I am accepting this as the solution although it has not been tested live yet.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.