ILM failed post upgrade

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

ILM Failed with the following error in the PWP log: 

20201103/000040.463 - U00005353 ILM: Processing after system stop was called with option 'CY' .
20201103/000040.479 - U00029125 UCUDB: Terminated the database connection because the user has been changed. Connection will be reestablished with user '<DOMAIN>\<USER>'.
20201103/000040.635 - U00003590 UCUDB - DB error: 'SQLDriverConnect', 'SUCC+INF', '01000', 'Changed database context to '<DB_USER>'.'
20201103/000040.635 - U00003592 UCUDB - Status: '01000' Native error: '5701' Msg: 'Changed database context to '<DB_USER>'.'
20201103/000040.635 - U00003590 UCUDB - DB error: 'SQLDriverConnect', 'SUCC+INF', '01000', 'Changed language setting to us_english.'
20201103/000040.635 - U00003592 UCUDB - Status: '01000' Native error: '5703' Msg: 'Changed language setting to us_english.'
20201103/000040.635 - U00029124 UCUDB: Successfully reestablished a database connection after the user has been changed. Processing continues with user '<DOMAIN>\<USER>'.
20201103/000040.635 - U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: a2ecbe0  DB-HDBC: a2ea220
20201103/000040.635 - U00003591 UCUDB - DB error info: OPC: 'SQLExecDirect' Return code: 'ERROR'
20201103/000040.635 - U00003592 UCUDB - Status: '42000' Native error: '300' Msg: 'VIEW SERVER STATE permission was denied on object 'server', database 'master'.'
20201103/000040.635 - U00003592 UCUDB - Status: '42000' Native error: '297' Msg: 'The user does not have permission to perform this action.'
20201103/000040.635 - U00003592 UCUDB - Status: '42000' Native error: '16945' Msg: 'The cursor was not declared.'
20201103/000040.635 - U00003594 UCUDB Ret: '3590' opcode: 'SLCB' SQL Stmnt: 'SELECT session_id AS DIVDB_PK, Object_name(resource_associated_entity_id) AS DIVDB_String, host_name AS DIVDB_String2, host_process_id AS Divdb_Int4, program_name AS DIVDB_String3 FROM sys.dm_tran_locks l INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id WHERE l.resource_type = 'OBJECT' AND s.database_id = db_id() AND l.resource_associated_entity_id IN (SELECT Object_id(table_name) FROM uc_table WHERE table_ilmflag IN ( 'S', 'R', 'M' ))'
20201103/000040.635 - U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR   ', '42000', 'The cursor was not declared.'
20201103/000040.651 - U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR   ', '42000', 'The cursor was not declared.'
20201103/000040.682 - U00003590 UCUDB - DB error: 'SQLDriverConnect', 'SUCC+INF', '01000', 'Changed database context to '<DB_USER>'.'
20201103/000040.682 - U00003592 UCUDB - Status: '01000' Native error: '5701' Msg: 'Changed database context to '<DB_USER>'.'
20201103/000040.682 - U00003590 UCUDB - DB error: 'SQLDriverConnect', 'SUCC+INF', '01000', 'Changed language setting to us_english.'
20201103/000040.682 - U00003592 UCUDB - Status: '01000' Native error: '5703' Msg: 'Changed language setting to us_english.'
20201103/000040.682 - U00029124 UCUDB: Successfully reestablished a database connection after the user has been changed. Processing continues with user ''.
20201103/000040.682 - U00005334 ILM: Server processing will be continued.

Environment

Release : 12.3, 21.x, 24.x

Component : AUTOMATION ENGINE

Cause

The database user does not have the access to the system views sys.dm_tran_locks and sys.dm_exec_sessions

In version v12 new checks on the database were introduced to verify the locks on ILM tables before proceeding with the switch.

Resolution

The user is missing access to the system views sys.dm_tran_locks and sys.dm_exec_sessions

Ensure that the database user(s) specified in the odbc-dsn and in the login has access privileges to these system views