Error while getting next item from queue

[DamianMastalerz]

Hi at all, I am getting a randomly error during VBO 'Work Queues -> Get next item' using in my process: ERROR: Internal : The EXECUTE permission was denied on the object 'usp_getnextcase', database '...', schema 'dbo'. Process has been started via 'Control Room'. Queue has been filled as well. The message says that the permission was denied on the object but I cannot understand why it happen randomly. If I wait a couple of minutes and start the process again it is working as expected. Item by item is being processed. If I would get the permission denied message every time then it would be clear. I am desperate. I asked my DBA if she could see anything stange happen on the database but she could not see anything (verbose logging activated on database). Has anyone ever had this problem or knows a solution or maybe can give me a hint? Many thanks in advance.

[John__Carter]

Is DB server coping with the size of the DB? Sometimes if a DB has grown so big that the server hosting it begins to struggle, you can get odd performance issues. Have you got a very high volume of items (across all queues)?

[DamianMastalerz]

First of all many thanks! At the moment there is only one queue with round about 100 items in our test environment. I will let check the size of the db but I guess it will be very low.

[John__Carter]

OK well it can't be the size of the DB if you've just started out. Perhaps then the account being used to connect to SQL - does it have the necessary SQL permissions? But the intermittent thing is odd, if the permissions weren't right you'd expect it to fail every time.

[PankajPuranik]

Screenshot 2

[PankajPuranik]

We had this exact issue however it was not intermittent. The issue happened because the schema of the stored procedure was not dbo. So even though the stored procedure was present, we got the following error. [2019-01-12 06:51:36Z] Session (ID: 63723179-a8e1-41db-a35d-dbc7c71a33a3) terminated. Process Name: BTL Process; Reason: Internal : Could not find stored procedure 'usp_getnextcase'. When we looked into the database we saw that the schema for some of the stored procedures was not set as dbo as can be seen below. See screenshot 1 We then did an ALTER SCHEMA to change it to dbo and that fixed the issue. See screenshot 2

[EncoTrio]

Hello,

The EXECUTE permission was denied on the object 'usp_getnextcase', database '...', schema 'dbo' realted user has not permission to run "usp_getnextcase" stored procedure in '...' database. If you are database admin then you need to give permission to the user performing the execution like below.

use [...]
GO
GRANT EXECUTE ON [dbo].[usp_getnextcase] TO [UserYY]

You can customize the above query by changing the use [...] and [UserYY] as you need.

The database administrators recommend other solution to create a new database role like db_executor. This is explained on the following blog source.

Thnx

------------------------------
Enco Trio
------------------------------