Audit Security Report in Excel

Complaints are often received regarding the structure of the out of the box security reports in GP. In order to get the full picture, multiple reports need to be run. In addition, the out of the box reports don't export nicely to Excel, requiring quite a bit of manual work to convert it to a nice format.

In order to consolidate this data into an efficient format, a SQL view can be used. The SQL view can then be easily utilized with SmartList Designer to make it available within GP SmartList (or Excel Refreshable Reports).

There is one preliminary step that must be completed, however, for the view to work properly. The steps are listed below.  Please note that administrator level security is needed in order to access the windows listed.

1. Go to Microsoft Dynamics GP > Maintenance > Clear Data

2. Click Display on the toolbar and click Physical

audit security report in excel - image1.png

3. Select System under Series

4. Click Security Resource Descriptions under Tables to highlight it and click Insert to add it to the Selected Tables list

audit security report in excel - image2.png

5. Click OK, then Yes to the pop-up message asking you if you’re sure that you want to clear data from the table

audit security report in excel - image3.png

6. Send the report to the screen, it should report back with ‘No errors found’

Now you can use the view below. The view will still work without this table being populated, but all the resource details will be blank. Create this view against the DYNAMICS database.

USE [DYNAMICS]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create VIEW [dbo].[view_Security_Details]

AS

SELECT DISTINCT

A.USERID UserID, A.CMPANYID CompanyID,

C.CMPNYNAM CompanyName, S.SecurityRoleID,

coalesce(X.CREATDDT,'') SecurityRoleCreatedDate,

coalesce(X.MODIFDT,'') SecurityRoleModifiedDate,

coalesce(X.MDFUSRID,'') SecurityRoleModifiedUser,

coalesce(T.SECURITYTASKID,'') SecurityTaskID,

coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName,

coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription,

coalesce(TM.CREATDDT,'') SecurityTaskCreatedDate,

coalesce(TM.MODIFDT,'') SecurityTaskModifiedDate,

coalesce(TM.MDFUSRID,'') SecurityTaskModifiedUser,

coalesce(R.DICTID,'') DictionaryID,

coalesce(R.PRODNAME,'') ProductName,

coalesce(R.TYPESTR,'') ResourceType,

coalesce(R.DSPLNAME,'') ResourceDisplayName,

coalesce(R.RESTECHNAME,'') ResourceTechnicalName,

coalesce(R.Series_Name,'') ResourceSeries

FROM SY60100 A

LEFT OUTER JOIN

SY10500 S

ON A.USERID = S.USERID and A.CMPANYID = S.CMPANYID

LEFT OUTER JOIN

SY01500 C -- company master

ON S.CMPANYID = C.CMPANYID

LEFT OUTER JOIN

SY10600 T -- tasks in roles

ON S.SECURITYROLEID = T.SECURITYROLEID

LEFT OUTER JOIN

SY09100 X -- role master

ON S.SECURITYROLEID = X.SECURITYROLEID

LEFT OUTER JOIN

SY09000 TM -- tasks master

ON T.SECURITYTASKID = TM.SECURITYTASKID

LEFT OUTER JOIN

SY10700 O -- operations in tasks

ON T.SECURITYTASKID = O.SECURITYTASKID

LEFT OUTER JOIN

SY09400 R -- resource descriptions

ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE

AND O.SECURITYID = R.SECURITYID

GO

GRANT SELECT on view_Security_Details to DYNGRP

After the view has been created, go back into GP to add it to SmartList (if desired).

1.  Go to Microsoft Dynamics GP SmartList > New Button:

audit security report in excel - image4.png

2. Give the List a Name and select the Product and Series to list the new report under.

3. Scroll down to the bottom of the Database View list on the left pane, expand Views, expand Company and scroll to check the box next to the view created above.

audit security report in excel - image5.png

After creating the report, the applicable users will need to be granted security. The DYNGRP line in the sql script for the view, will take care of part of the security access the user will need to view the results of this list. The second part is from within GP.

The following access needs to be added to a new or existing security task by going to Microsoft Dynamics GP > Tools > System > Security > Security Task Setup. Select or create a new Task. Select Smartlist as the Product, select Smartlist Object as the Type, and SmartList Objects as the Series. Check the box for the new SmartList created in the steps above.

audit security report in excel - image6.png

If you need any assistance completing any of the steps above, do not hesitate to contact us at support@costapartnersllc.com.