March 20, 2009 
As a corollary to my view showing the Dynamics GP security roles and tasks assigned to users, I have created another view that adds the security resource details. Thank you very much to Tim Foster for pointing me to a newsgroup post by David Musgrave detailing how to populate the SY09400 table in SQL which holds the system resource details for GP 10.0 and later. I have listed the instructions below:
-
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’
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. (Update on May 21, 2015: Please note that even though this was originally written for GP 10.0, this will also work for GP 2010, GP 2013 and GP 2015.)
CREATE VIEW view_Security_Details AS -- view_Security_Details -- Created Mar 20 2009 by Victoria Yudin - Flexible Solutions, Inc. -- For updates see https://victoriayudin.com/gp-reports/ -- Shows all security roles, tasks and detailed resource descriptions by user by company SELECT DISTINCT S.USERID UserID, S.CMPANYID CompanyID, C.CMPNYNAM CompanyName, S.SecurityRoleID, coalesce(T.SECURITYTASKID,'') SecurityTaskID, coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName, coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription, 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 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 --the following will grant permissions to this view to DYNGRP, leave this section off if you do not want to grant permissions GO GRANT SELECT ON view_Security_Details TO DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
For other SQL code, please check out the GP SQL Scripts section my GP Reports page.
Victoria Yudin 
I created this view to use in Builder. The view is in the dynamics database when i look in SQL, but when i go to builder to choose the view it’s not there? Any ideas?
LikeLike
Hi Stephanie,
In SmartList Builder you first have to give it permissions to the view under SQL Table Security. Choose the DYNAMICS database, then select Views on the top right and you should see the view.
– Victoria
LikeLike
Victoria, thank you so much for the quick response! I tried that and when i go to sql table security and choose the dynamics database i see no views available. I tried it on a clients and mine, and same results both places. I’m admin on my machine and signed in as SA.
LikeLike
Hi Stephanie,
Did you make sure to give DYNGRP permission to the view in SQL?
-Victoria
LikeLike
Victoria,
I did run the grant script. The weirdest thing is i did the other view Security Roles and Tasks and it shows up fine for use in builder. Super odd, usually views and builder work flawlessly so i’m scratching my head.
LikeLike
Hi Stephanie,
Very strange. Are you able to run it directly in SQL using the following?
select * from DYNAMICS.dbo.view_Security_Details
LikeLike
Yep, works like a dream when i do that. Our workaround is we copied the data from the query, but sure would be lovely to get it into builder.
LikeLike
Hi Stephanie,
I just tested setting it up in my GP as a new SmartList using SmartList Builder and it worked just fine. If it’s not working for you, you may need to reach out to eOne support or your GP partner for help with it.
-Victoria
LikeLike
I’m starting to pursue options for security audit reports at a company I just joined. I’m seeing many SQL suggestions of joining SY01500 and SY01600 on securityroleid, but I’m finding only 1 out of 5 securityroleids in SY01600 exist in SY01500, Can anyone add some insight into that issue for me?
LikeLike
Why after creating this view and then giving a user access to all tables involved, and the smartlist that the view was built from, would it still be saying that they don’t have access to all of the tables necessary to run this smartlist?
LikeLike
Hi JoAnn,
Have you tried going through all the steps listed in this blog post?
-Victoria
LikeLike
Yes, I have. The tables and views are all marked on for access, as well as all the tables listed in the task for the smartlist and applied to the user. We cannot seem to get past the message regarding not having access. Could the SY09400 table have anything to do with it? However I do have that one marked on also….
LikeLike
JoAnn,
You should not have to give permissions to individual users/tables. DYNGRP permissions to the view should be enough. Does the SmartList work for ‘sa’? What about a user who has the POWERUSER role in GP? Also, what is the exact wording of the error you are getting?
-Victoria
LikeLike
I get this message >>> A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
LikeLike
Steve,
Looks like the code was being wrapped incorrectly, sorry about that. I have changed the formatting of the code so that it should copy better. Please let me know if you run into any more issues.
-Victoria
LikeLike
Victoria, thanks for this script! FWIW, after a little hunting around, I found that table SY09100 contains the security role names, which I’m adding to your script for a report I’m creating,
Best, Michael
LikeLike
Thanks for the info Michael!
-Victoria
LikeLike
Hi Victoria,
I try your query , still some fields are empty ….. SY10700 is null …
Any other way to view what are the operations & corresponding users list
LikeLike
Some fields may come back blank in this query – this will depend on your security settings. However, if your SY10700 table is empty, that might signal a different issue. What version of GP are you using? Also, did you make sure to go through the 6 steps outlined in the beginning of the post?
-Victoria
LikeLike
Hi Victoria,
This script is so cool ! Tank you so much.
One question, how do I find informations about the tables ? I want to know who has access to the table RM00101 for exemple.
What would I need to change in your script to be able to see such infos ?
Thank you for your time.
Sebastien
LikeLike
Sebastien,
I am not sure there is any easy way to see this in SQL. I will post a question about this and see if I can get a reponse from Microsoft.
-Victoria
LikeLike
Hi Sebastien,
I heard back from Microsoft that there is no way to see this information without a Dexterity customization. 😦
-Victoria
LikeLike
Hi Victoria,
Thank you for your research.
I have found a tool called “Support Debugging Tool for Dynamics GP” by David Musgrave; in this tool, I can search for a resource and then get security informations on that resource.
When I search for a form/window, I can get in which security role/task it is part of; but when I look for tables, the security informations it returned are for the table group only. I was hoping to get the info I wanted through that tool.
Have you looked into that tool ?
I will contact David Musgrave and try to get more information from him.
Thank you again and have a woderful day !
Seabstien
LikeLike
Sebastien,
David Musgrave was actually the person at Microsoft that told me it was not possible to get the information you’re looking for without writing Dexterity code. He has already confirmed that this is not currently possible with the Support Debugging Tool.
-Victoria
LikeLike
Hi Yudin,
Somehow I am not able to see exact information from table SY09400. I have created table as well.
I have created one Role and assigned access to following window.
1. Payable Trx Entry
2. General Entry
Product : Great Plains
Type : Series Posting Permission
Series : Financial
The above data is not fetching in view due to wrong data populated in Resource table SY09400.
Regards,
Santosh
LikeLike
Santosh,
Did you assign this newly created role to anyone? This view is meant to show what security is assigned to what user. If you simply created the role, you will not see it in the results.
-Victoria
LikeLike
Hi Victoria,
Yes I have assigned this role to me and I am not using ‘sa’ or any super user (Poweruser role).
I have just created new user and assigned PA BILLING CLERK* and for posting this new role.
Regards,
Santosh
LikeLike
Hi Santosh,
Have you tried repopulating the data in the SY09400 table by going through the 6 steps I outlined?
-Victoria
LikeLike
Hi Victoria,
Yes I followed the same step which you have mentioned.
Regards,
Santosh
LikeLike
Santosh,
And your SY09400 table, if you look at it in SQL, has data?
-Victoria
LikeLike
Hi Victoria,
Yes table SY09400 contains data. Infact I have re-created entire table and verify data for new created Roles. But no luck for new roles it is not showing any description or detials for new roles.
Regards,
Santosh
LikeLike
Hi Santosh,
I am not really sure where to go from here in a forum like this. It sounds like something is going wrong, but without looking at your system, I am not sure if anyone could help further. My next step would be to talk to your GP Partner or Dynamics GP support to see if they can help you.
-Victoria
LikeLike
Hi Victoria,
Thanks for your time on this. Let me see how I can resolved this.
Are you able to see data into table SY09400 in your system?
I mean if you create one Role and assign following task/posting window.
Product : Great Plains
Type : Series Posting Permission
Series : Financial
1. Payable Trx Entry
2. General Entry
Regards,
Santosh
LikeLike
Santosh,
You will not see newly created roles in the SY09400 table, since it’s storing resources, not roles. However, if you create the view I have above and assign a new role to a user you should be able to see it in the results of the view.
-Victoria
LikeLike
Do you know of any way to include Smartlist objects in the SY09400 table?
LikeLike
Steven,
I can see that when the security changes for SmartList objects, the changes go into the SY10700 table, but I don’t see anywhere to get the detail of what the SmartList objects are. If anyone reading this has the answer, please let me know and I will update my post.
-Victoria
LikeLike