Report permissions error : MODIFYALL

gemini_shooter

Hello All,

I get an report permissions error "You do not have permission to modify the G/L Entry Table."

I am super user and have ample SQL rights. The database uses database username and password. I also checked the license and the client has 3010 granule for General Ledger.

The reports errors out at at a G/L entry MODIFYALL command. Any clues?

David_Singleton

No way should you be modifying GL Entries. What ever you are thinking of doing, contact your partner and rethink this.

Modify all on GL Entries could destroy your database, which is why it is protected.

gemini_shooter

hmmm..Its not a big change it just updates a custom field to closed.

What do you mean when you say that MODIFY ALL is protected?

DenSter

You need to have a partner license to make direct changes to the G/L Entry table. A normal customer license will not have permission to run that report.

gemini_shooter

Den,

This used to work fine for us before. We have custom field in the G/L Entry and we update it to reviewed/closed based on when it has been reviewed by the accountants.

I do understand that G/L Entry's are critical, especially if they are tied with the sub-ledger. However, I am trying to understand why did this report stop working?

Modify all on GL Entries could destroy your database, which is why it is protected.

--Based on what David said, I went and ran the report with client monitor to see what SQL statement was sent to the DB and NAV is sending SELECT with (UPDLOCKS) and THEN UPDATE SET, which I do understand. The users have the Public role in SQL, now I am starting to wonder if public is enough to do updates? Do I need db_datawrite?

--But you are saying that I need a partner license to run this report? hmmm.. so G/L Entry updates are not allowed in client license, even through a report? Can you shed some light on this concept,

--What is the granule/line in the partner license that separates this direct change feature vs. a client license?

--Are there other tables that follow the same behavior as the above?

Thank you for the helpful comments, I am learning something new here, so please excuse my excitement :-)

David_Singleton

Imagine a question on a guns forum.

"Hi all, I am new to guns, but I borrowed a shotgun recently and it wont work. I am looking down the barrel and pulling the trigger but I can't see anything happen. It is loaded, and I saw it working few weeks ago at the shooting range. Can some one tell me what I am doing wrong."

What is the correct answer?

A You need to take it off SAFE by moving the safety catch to FIRE.
B You should be doing this out doors.
C You should get a gun expert to give you some lessons.

FDickschat

Take a look at this thread to see what is required to write code which can modify protected tables:
http://www.mibuso.com/forum/viewtopic.php?t=41502

gemini_shooter

Thanks FD, however the code is already written in a report, which is custom report.

The report runs a MODIFYALL command on FINDSET and updates a custom field in G/L entry.

Do you need solution developer even to run the report?

I do understand you need solution developer if you are modifying the table but thats not the case, it is already modified.

ssingla

gemini_shooter wrote:

Thanks FD, however the code is already written in a report, which is custom report.

The report runs a MODIFYALL command on FINDSET and updates a custom field in G/L entry.

Do you need solution developer even to run the report?

I do understand you need solution developer if you are modifying the table but thats not the case, it is already modified.

Table have 2 parts : a) Table b) Table data.
Simple client license will not have permission to modify protected tables. If the report was working earlier for the client then the server must be having Developer License or client must be changing to dev. license before running the report.

gemini_shooter

Thank you all for the helpful comments.

I was able to find the issue in the report and fix it.

Alex_Chow

End user licenses cannot modify ledger entry tables. Only solution center license can modify ledger entry tables.

The solution center license and create objects that can modify ledger entry tables that can be ran by end users.

David_Singleton

gemini_shooter wrote:

Thank you all for the helpful comments.

I was able to find the issue in the report and fix it.

Ah so did you get access to a developer license then?

gemini_shooter

It was a issue in Report Properties permission G/L Entry was missing, partner can add that and the report should be backup and running

kapamarou

gemini_shooter wrote:

It was a issue in Report Properties permission G/L Entry was missing, partner can add that and the report should be backup and running

We've had similar issues with Value Entry because the localization uses Extra fields on value entry to update the item cost.

I think this topic is really interesting if we read it relation to this: viewtopic.php?f=34&t=44719
and then compare two options with a similar result:

a) A field that is updated directly on "G/L Entry"
b) A secondary table that has an entry for each "G/L Entry" indicating the review status and maybe a flowfield on table "G/L Entry".

I honestly don't know what is better: A or B? Maybe there should be some testing in order to figure this out depending on usage (How often is the table updated? Is your field used very often as a filter?...). Any thoughts?

gemini_shooter

Very nice link kapamarou =D>

I had a similar issue with Sales Header for a client with table running close the 4000 key limit (5.0) so we extended it using b), not the best solution but I believe that limit is 8000 in 2009.

Most of the other times, I see developers sticking to a)

But I always wonder if it would be possible to use a) as a standard, because then you can totally separate your custom tables in a range. That might be helpful in upgrades as well, but am sure it might bring some pain due to the join queries on performance.

David_Singleton

Gemini are you a partner or an end user?

gemini_shooter

None, Contractor right now

David_Singleton

gemini_shooter wrote:

None, Contractor right now

OK,

because this is the sort of stuff that a customer really wouldn't know. But as a partner its pretty much Navision 101, so any Navision developer would know about permission properties. I guess you are doing the functional side then. In which case its probably best you don't "play around under the hood", and get a developer to look at this for you instead. I have seen functional consultants do some major damage playing with the developer's tools. Just using the standard report designer its possible to do some serious damage.

ssingla

David Singleton wrote:

I have seen functional consultants do some major damage playing with the developer's tools. Just using the standard report designer its possible to do some serious damage.

:-#

gemini_shooter

Appreciate the concern David, but I think this is a forum for learning and sharing knowledge so I would never hesitate in asking something, even if people are not willing to share the knowledge.

Whats the worst that can happen.. I won't get a straight answer..

Slawek_Guzek

The straight answers are:

a) you need to have developer license to run custom report modifying G/L entries.

b) you need to have developer license to set report-level permissions on custom report to allow it modifying G/L when run under customer license.

Slawek