Dynamics GP Essentials: Attachments Report

Mahmoud AlSaadi brings us a new GP 2013 R2 Attachments Report. A lot of people have asked me where the attachments are stored and after using Mahmoud’s report as a starting point, it appears that they are stored in table named coAttachmentItems.

 

 

 

 

 

 

 

 

 

 

Dynamics GP Land: Dynamics GP takes over 10 minutes to login or switch companies (aka Don’t blame Mekorma MICR)

Steve Endow tackles a case where Dynamics GP takes over 10 minutes to login or switch companies (aka Don’t blame Mekorma MICR).

Weekly Dynamic: Create a Super User

Fastpath has written about minimizing the ‘sa’ user in the past This led to more people using Power User. They’ve also done a good job of highlighting that Power User really isn’t a role, it’s an override. If a user is a Power User the system effectively skips the security check. This also means that Power User doesn’t appear as a user with access to specific items when you run security reports. That can be a problem for the auditors. Also, you can’t limit what a Power User can do.

One option is to create your own Super User, and while it’s not hard, there are a few quirks.

1) Clear the SY09400 table. Victoria Yudin has specifics.

This repopulates the table with the current security resources. There is additional code that runs if the newest version of the Support Debugging Tool is installed to add items beyond just forms and reports.

2) Run the SQL Script below. This adds a Super User role and assigns security to all of the items, even if the description hasn’t been added to the SY09400 table. I still can’t swear that this catches everything, but it should and it worked in testing. Worst case, it’s a whole lot easier to start with this than to check everything manually. You’ll still need to assign users to this role, but now it’s a real role, You can report off of this role and remove specific access as necessary.

–Create SUPERUSER Task
Insert into SY09000 (SECURITYTASKID,SECURITYTASKNAME,SECURITYTASKDESC,SECURITYTASKCATEGORY,DEFSECTASK,CRUSRID,CREATDDT,MDFUSRID,MODIFDT)
Values (‘SUPERUSER’,’SUPERUSER’,’Super User task to replace Power User role’,7,0,’sa’, cast(getdate() as date), ”, ‘1/1/1900’)

–Assign all security items to SUPERUSER task

Insert Into SY10700 (SECURITYTASKID,DICTID,SECURITYID,SECRESTYPE)
Select distinct ‘SUPERUSER’,a.DICTID,a.SECURITYID,a.SECRESTYPE from
(select distinct DICTID,SECURITYID,SECRESTYPE from SY10700
union
select distinct DICTID,SECURITYID,SECRESTYPE from SY09400) a

–Create SUPERUSER Role

Insert into SY09100 (SECURITYROLEID,SECURITYROLENAME,SECURITYROLEDESC,SECROLETYPE,CRUSRID,CREATDDT,MDFUSRID, MODIFDT)
Values (‘SUPERUSER’,’SUPERUSER’,’Super User Role to replace Power User role’,2,’sa’,cast(getdate() as date), ”, ‘1/1/1900’)

–Assign SUPERUSER Task to SUPERUSER Role
Insert into SY10600 (SECURITYROLEID,SECURITYTASKID)
Values (‘SUPERUSER’,’SUPERUSER’)