I have created a report using Crystal Reports XIr2 to report on information from a database for our application. It is a cross tab report, reporting on a variety of data returned from running a stored procedure.
A quick bit about the application that this report is run through - it is an application written in VB6, upon loading the application you are prompted for a username and password, which are configured on a SQL server as SQL logins, so when you log into the application you are authenticating against SQL server security.
The report is working absolutely fine in Crystal Reports, however I am experiencing some weird behaviour with this report when it is launched from within our application on a customer site. It works fine for all users with 'sa' level database access but gives the following error when run as any other user;
If I connect to the database server directly as a user that gets the above error within the application, running the stored procedures manually works without any errors.
If I give a user that is getting the error within our application 'sa' permissions on the SQL server, the error stops happening.
I have checked all related execute permissions for the stored procedure in question, but as stated if I connect to the SQL Server manually and execute the procedure, I do not get the error linked above.
If the user who is getting this error runs another report before this report, it works, however if they run a report after getting this error, all reports stop working and require an application restart.
Any ideas please?