1,821
Hi,
We need to remove the login access in GP, if user does not use GP for 10 minutes. Is ther nay way we can do this. Kindly help me .... with details ..
Regards,
Noushad
*This post is locked for comments
Use at your own risk:
Share
Report
All responses (
Answers (
DECLARE @tTable TABLE (sqlsvr_spid INT);
INSERT @tTable
SELECT
S.sqlsvr_spid
FROM DYN18..ACTIVITY A
LEFT JOIN DYN18..SY01400 U on A.USERID = U.USERID
LEFT JOIN DYN18..SY01500 C on A.CMPNYNAM = C.CMPNYNAM
LEFT JOIN tempdb..DEX_SESSION S on A.SQLSESID = S.session_id
LEFT JOIN master..sysprocesses P on S.sqlsvr_spid = P.spid and ecid = 0
LEFT JOIN master..sysdatabases D on P.dbid = D.dbid
WHERE datediff ( mi, P.last_batch, getdate() ) >= 30;
DELETE A
FROM DYN18..ACTIVITY A INNER JOIN
(
SELECT
CASE
WHEN S.session_id is null
THEN 'Missing DEX_SESSION'
WHEN A.USERID <> P.loginame or P.loginame is null
THEN 'Phantom'
ELSE ''
END MismatchOnUserID,
CASE
WHEN datediff ( mi, P.last_batch, getdate() ) > 90
THEN 'Idle ' + str ( datediff ( mi, P.last_batch, getdate() ) )
ELSE ''
END AS Working
, A.USERID
, A.CMPNYNAM
, INTERID
, LOGINDAT + LOGINTIM LoginDatestamp
, SQLSESID
, P.login_time
, P.last_batch
, datediff ( mi, P.last_batch, getdate() ) SinceLastAction
, S.session_id
, S.sqlsvr_spid
, P.spid
, P.status
, P.net_address
, P.dbid
, P.hostname
, P.loginame
FROM DYN18..ACTIVITY A
LEFT JOIN DYN18..SY01400 U on A.USERID = U.USERID
LEFT JOIN DYN18..SY01500 C on A.CMPNYNAM = C.CMPNYNAM
LEFT JOIN tempdb..DEX_SESSION S on A.SQLSESID = S.session_id
LEFT JOIN master..sysprocesses P on S.sqlsvr_spid = P.spid and ecid = 0
LEFT JOIN master..sysdatabases D on P.dbid = D.dbid
) SQL_ACTIVITY ON (A.USERID = SQL_ACTIVITY.USERID)
WHERE SQL_ACTIVITY.SinceLastAction >= 30
DELETE FROM tempdb..DEX_SESSION WHERE sqlsvr_spid IN (SELECT sqlsvr_spid from @tTable)
You can schedule a SQL job to run this script, or you can preferably create a stored procedure with it and schedule a SQL job to run the stored procedure, rather. Please at least test it in a test environment, before deploying into production.
There is no native way to do this safely without a 3rd party product.
Tim.
Hi All,
Thanks for your reply. I will make it clear my requirement. if the user kept GP idle (without any activity) for 30 minutes, there should be some automation to remove his GP user access from GP or logout automatically from GP. So other user can use his access. This is the objective of this requirement.
Kindly help me. Is there any way we can achieve this requirement other than using 3rd party product.
Regards,
Noushad
I use the GP Toolbox, works well. I tested the feature and other features in GP Toolbox, no issues. I do not use this specific log off feature. Rockton support are superb also
Do you mean log the user out rather than deactivating the account?
If this is the case then Rockton have a tool in the toolbox product for this:
#1
Almas Mahfooz
3
User Group Leader
