Standard Code:
One of the simple ways to remove unused users from Great Plains is done by using the following code:
Delete from Activity where userid = ‘XXXX
”
This code does not really remove the unused user and all of the activity associated with the user. This code just removes the record from the activity table so that Dynamics GP does not see the user. There might be a possibility of removing wrong user id via SQL or GP. This can happen if the user is logged into 2 or more different companies at the same time.
The User sessions with the ACTIVITY table is available in SYSPROCESSES and few TEMP tables are available in DEX_SESSION and DEX_LOCK.
Best Code:
Use the following code to Remove Unused User Login Sessions in Microsoft Dynamics Great Plains.
- delete from DYNAMICS.ACTIVITY
- where USERID not in (select loginame from master.sysprocesses)
- delete from tempdb.DEX_SESSION
- where session_id not in (select SQLSESID from DYNAMICS.ACTIVITY)
- delete from tempdb.DEX_LOCK
- where session_id not in (select SQLSESID from DYNAMICS.ACTIVITY)
- delete from DYNAMICS.SY00800
- where USERID not in (select USERID from DYNAMICS.ACTIVITY)
- delete from DYNAMICS.SY00801
- where USERID not in (select USERID from DYNAMICS.ACTIVITY)
CAUTION: When you run this code, make sure the database backup is already completed.
No Comments