April 3, 2015 at 5:43 am
Our auditors are trying to enforce a requirement that all users be disconnected from any application or database after 15 minutes of inactivity. Is there any way to force a logoff within SQL Server? For example, someone is in Management Studio connected to a database. They should be logged off after 15 minutes of no activity.
April 3, 2015 at 10:35 am
Aren't auditors fun? don't know why they would have such a requirement but many, many moons ago I wrote a script based on last_batch time in the much loved master..sysprocesses. To be honest sys.dm_exec_sessions would be the way to go now.
amend as necessary
print 'The following MTS spids have a last active time older than 30 minutes and will be killed'
print ' '
declare @program_name char(30),
@userid char(10),
@spid smallint,
@last_batch datetime,
@date datetime,
@no_of_mins int,
@occurred char(1),
@statement nvarchar (10)
select @date = getdate()
set @occurred = 'N'
declare killmts_cursor cursor for
select spid, convert(char(10),nt_username), convert(char(30), program_name), last_batch
from sysprocesses
where program_name = 'microsoft transaction server' --delete\amend as necessary
open killmts_cursor
fetch next from killmts_cursor
into @spid, @userid, @program_name, @last_batch
while @@fetch_status = 0
begin
select @no_of_mins = datediff(mi, @last_batch, @date)
if @no_of_mins > 29
begin
print convert(char(3),@spid) +' ' +@userid
set @statement = N'kill ' + convert(nvarchar(3),@spid)
exec sp_executesql @statement
set @occurred = 'Y'
end
fetch next from killmts_cursor
into @spid, @userid, @program_name, @last_batch
end
close killmts_cursor
deallocate killmts_cursor
---------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply