This article is obviously a follow up to Permission Problems post I published previously. The other day I was using the User Permissions Report (RPUSER), I noticed something amiss. I had recently deleted a couple of educational and testing companies which many people used. However, like many other areas of M2M, the records are not properly deleted and cleaned up.
This becomes a problem when you run the User Permissions Report because it lists every single company, every user associated with it, and the exhaustive list of screens with their permissions. It includes every user and company even after deleting them from within M2M. This slows down the report and clutters the results.
Below is the code one could use in Visual FoxPro to clean this up. Please read my Standard Disclaimer before trying any code from this site. I’m going to stress this several times because of an issue that came up as a result of the orphaned records post. Stay tuned for details.
Set excl on – You must have exclusive access to perform this.
OPEN DATABASE X:\m2mdata\util\util.dbc – Replace X with the network drive letter assigned to M2M.
Use utacc
Copy to utaccOLD
Brow for fccompid = ‘XX’ – where XX is the number of the M2M Company for which you intend to remove users and rights. Make sure you do NOT do so on your live M2M Company. This will show the records that will be deleted.
Delete for fccompid = ‘XX’ – Remember, don’t use this on your live data.
Pack – This will make the deletion permanent.
Close all
Quit.
Please remember to test this on a test server, and if you feel uncomfortable contact M2M Support or a qualified consultant such as myself for help.
Any questions?
Great tip! This is really a huge performance issue that many struggle with. Happy that you’ve pointed this out. And yes, be sure to use a test system! Messing with the UTIL directory will cripple your database quickly, if you are not careful – even when you “know” what you’re doing!
you can use the filter to get only the company you want.
talking on the topic of permission how I can generate a report per screen that will show only the user that have at least one permission and not showing all users wit no permission at all!!
Yes, you’re right Avi, but my point was, should you really have to filter them out? There are many problems with those filters too. You cannot copy and paste them for instance. I’ll have to think about your question and get back to you.
[...] Web Support Spotlight Days Purging Pointless Permission Records Provides Performance Payoff [...]