How to find the User Security with Associated Screens/Resource Names in Dynamics GP?

The below code will helps you to find the user security with all the appropriate resource Id and Names.

SELECT DISTINCT S.USERID UserID,

S.CMPANYID CompanyID,

C.CMPNYNAM CompanyName,

S.SecurityRoleID,

T.SECURITYTASKID SecurityTaskID,

TM.SECURITYTASKNAME SecurityTaskName,

TM.SECURITYTASKDESC SecurityTaskDescription,

R.DICTID DictionaryID,

R.PRODNAME ProductName,

R.TYPESTR ResourceType,

R.DSPLNAME ResourceDisplayName,

R.RESTECHNAME ResourceTechnicalName,

R.Series_Name ResourceSeries FROM SY10500 S — security assignment user role

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 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

Note: Before execute the code against your system database follow the below steps:

  • Go to Microsoft Dynamics GP > Maintenance > Clear Data
  • Click Display on the toolbar and click Physical
  • Select System under Series
  • Click Security Resource Descriptions under Tables to highlight it and click Insert to add it to the Selected Tables list
  • Click OK, then Yes to the pop up message asking you if you’re sure that you want to clear data from the table
  • Send the report to the screen, it should report back with ‘No errors found’

Hope this helps!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s