Sage 100

Sage 100 ERP v4.45 SQL. Created a SQL view and am using the view as my data source in a Crystal Report. The report runs fine when it is not attached to the MAS menu. When it is run from the menu the following error is displayed (see attached).

@DougHiggs I haven't run into this in the past but two things come to mind; 1) is the view in the company database and 2) does the mas_user login and database user have rights to the view that you created? I would check both of these to be sure. Your SQL logon probably is an SA equivalent and has default full-access to all data base objects, thus why you can run it from within Crystal directly (Crystal access SQL using your logon ID) where Sage 100 can't (It might use either the MAS_User or MAS_Reports login created during the software install.) I'm not positive that this will work but it is where I would start.

Thanks for your help Shawn. You pointed me in the right direction and I believe I have a solution. Yes, the view is part of the company database so that wasn't the issue. I have it working, however, I'm not sure this is a permanent solution. You were correct in predicting I was using the sa login to create and run the report from outside of MAS. As a test, I assigned the sysadmin server role to the MAS_Reports SQL login. That worked, but I'm not sure how secure it is. I will have to think about it.

@DougHiggs, I really would not recommend making MAS_Reports and member of the sysadmin Server role. That opens up a pretty big security hole. As an alternative, right click the view in question in SSMS and look at the Permissions page. Be sure the MAS_Reports user has Delete, Insert, References, Select, and Update permission under ""Grant"". I'm not sure all of theses rights are required but you can test to determine exactly what is required to execute the report by adding them all and then removing each one at a time, running the report in between. I'm positive you will need 'Select'. I'm not sure the others are required but that's where I would start. SQL is a different beast that Providex. It's important to remember that when you create an object within SQL, the users who needs to access them must have the rights to do so. If you plan to create a Crystal Report and let end users execute the report from outside of 100, each person needs a login defined on the SQL server under Security as well as be defined as a user in the database. Then, they need permissions to access each object. Most of this can be done via SQL scripting. (Don't forget that groups work well in SQL the same as they do in 100 and follow the same concepts.) Happy hunting.