Automic Workload Automation

Being a bank, we are only allowed to have DB reader and writer permissions on our SQL db in the higher environments.  This is why when I run the DB maintenance workflow for the first time, the DB Unload portion fails with:

20190409/103027.720 - U00037118 Starting with OFS cleanup for client ALL
20190409/103027.720 - U00037119 Finished OFS cleanup with status: OK
20190409/103027.720 - U00037029 According to [REORG], MAX_DEADLOCK='100' in the INI file, DEADLOCK situations will be restarted up to '100' times.
20190409/103027.736 - U00037111 Starting reorganization for client: 'ALL'
20190409/103027.767 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: 11161d0 DB-HDBC: 11162b0
20190409/103027.767 - U00003591 UCUDB - DB error info: OPC: 'SQLExecDirect' Return code: 'ERROR'
20190409/103027.767 - U00003592 UCUDB - Status: '42000' Native error: '1088' Msg: 'Cannot find the object "DIVDB" because it does not exist or you do not have permissions.'
20190409/103027.767 - U00003594 UCUDB Ret: '3590' opcode: 'EXEC' SQL Stmnt: 'truncate table DIVDB'
20190409/103027.767 - U00003590 UCUDB - DB error: 'SQLExecDirect', 'ERROR ', '42000', 'Cannot find the object "DIVDB" because it does not exist or you do not have permissions.'
20190409/103027.767 - U00037178 Error occurred in SQL-truncate of table DIVDB.
20190409/103027.767 - U00037107 Error during reorganization of table 'OH'. Program exits with error.
20190409/103027.767 - U00037022 ABORTING due to error.

Does anyone know how to truncate a table without DBO and ddladmin permissions?

Any advice/help anyone can provide, I will very much appreciate.  Thanks!

Hey Harriet,

I do not have anything to add to this particular issue with the DB maint jobs. However, you need to think about the scenario of having to do a COLD start of the system in the event something goes wrong with the AE. With a COLD start, the WP's are going to try and truncate the MQ tables as well as other system recovery processes like recreating agent entries and such.

Sure you could manually do that task but that would probably raise more alarm than letting a WP do it as designed.

Oh man, I didn't even think ahead about COLD starts lol.  Thanks for reminding me about that!  I Googled and found that I need at least the ddladmin permissions to be able to truncate.  I even tested it in my lower environment and ddladmin worked.  I presented the info to my DBA.  We'll see what they say...

Here is what I can see for this issue.

1. It looks like the WP is calling a stored procedure to do the truncate (call UC_Truncate_Table). So they could probably be more granular with that permission to allow for a cold start without grating the admin permissions.

2. As for the DB maint jobs. Maybe they can just put ALTER permission on that DIVDB table? Can provide evidence that it is just a dummy table as described in the documentation.

Your suggestion is the best feasible way address this issue.  Thank you, Michael_Pirson!  This is really helpful!

Oh!  One more thing, can you tell me where the stored procedure is located to do the truncate (call UC_Truncate_Table)?   

In MSSQL -> UC4 Database -> Programmability -> Stored Procedures

Thank you!!