We are migrating from Net Express 3.1 to Visual COBOL and found the following problem in Native code with embedded SQL statements. When we perform the open cursor for a fetch statement, we receive a sqlcode of -0000000229, sqlstate of 42000, and a mfsqlmessagetext of [Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'Customer', database 'Test', schema 'dbo'.
In the SQL database, we limit the users to only having connect permissions and the ability to execute a stored procedure that will return an approle username and password. Then, we execute sp_setapprole using the returned username and password to provide read and write access to the database. This process works just fine in Visual COBOL. Further, we can perform a SELECT statement returning a single row and the declare cursor. However, we get the above error message when we execute the open cursor.
This code works fine in Net Express 3.1. If we grant the users permission to datareader on the database, then everything runs without a problem. What do we need to change in order to achieve the same behavior in NE?