May 13, 2010 
Robert Cavill from Emeco in Australia has sent me an amazing update to my Security Roles and Tasks in Dynamics GP SQL script that includes SmartList objects. This script was originally written for GP 10.0 and GP 2010, but will also work with GP 2013 and GP 2015. To get the most out of this script you will want to populate the GP system resource table by following the steps below:
- Go to Microsoft Dynamics GP > Maintenance > Clear Data
- Click Display on the toolbar and choose 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”
The view will still work without going through the steps above, but all the resource details will be blank. Please note that the script below should be run against your DYNAMICS database.
~~~~~
CREATE VIEW view_Security_and_SL_Details AS /******************************************************************* view_Security_and_SL_Details Create this in the DYNAMICS database Created May 13 2010 by Victoria Yudin - Flexible Solutions, Inc. and Robert Cavill - Emeco For updates see https://victoriayudin.com/gp-reports/ Shows all security roles, tasks and detailed resource descriptions including SmartList by user by company for Dynamics GP *******************************************************************/ SELECT DISTINCT S.USERID [User_ID], S.CMPANYID Company_ID, C.CMPNYNAM Company_Name, S.SECURITYROLEID Security_Role_ID, coalesce(T.SECURITYTASKID,'') Security_Task_ID, coalesce(TM.SECURITYTASKNAME,'') Security_Task_Name, coalesce(TM.SECURITYTASKDESC,'') Security_Task_Description, coalesce(R.DICTID,SO.ASI_DICTID,'') Dictionary_ID, coalesce(R.PRODNAME,'') Product_Name, coalesce(R.TYPESTR,SO.ResType,'') Resource_Type, coalesce(R.DSPLNAME,SO.SmartlistObject,'') Resource_Display_Name, coalesce(R.RESTECHNAME,'') Resource_Technical_Name, coalesce(R.Series_Name,'') Resource_Series 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 LEFT OUTER JOIN -- smartlist objects (SELECT SECURITYTASKID, SECURITYID, DICTID, SECRESTYPE, ASI_DICTID, SL_OBJID, SmartlistObject, 'Smartlist' ResType FROM (SELECT SECURITYTASKID, SECURITYID, DICTID, SECRESTYPE, SECURITYID / 65536 ASI_DICTID, SECURITYID % 65536 SL_OBJID FROM SY10700 WHERE SECRESTYPE = 1000 AND DICTID = 1493) ST JOIN (SELECT coalesce(TRANSVAL, ASI_Favorite_Name) SmartlistObject, ASI_Favorite_Dict_ID, ASI_Favorite_Type FROM ASIEXP81 F LEFT JOIN ASITAB30 A ON F.ASI_Favorite_Name = A.UNTRSVAL AND A.Language_ID = 0 WHERE ASI_Favorite_Save_Level = 0) SM ON ASI_DICTID = ASI_Favorite_Dict_ID AND SL_OBJID = ASI_Favorite_Type) SO ON SO.DICTID = O.DICTID AND O.SECRESTYPE = SO.SECRESTYPE AND O.SECURITYID = SO.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_and_SL_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.
Thanks again to Robert for his work on this! For more SQL code and help with reporting on Dynamics GP data, please take a look at my GP Reports page.
Victoria Yudin 
Hello Victoria,
Do you know if there’s a way to find the SecurityID for a specific smartlist?
When we create a new smartlist, we would like to add it to the security task through SQL, but we haven’t found how to find the correct SecurityID.
The problem is we have over 40 clients, and each could have a different ID so we need to fetch their ID by using the smartlist name.
We can do it without issues through the UI of each client, we would LOVE a way to do it during the deployment of the smartlist.
LikeLike
Hi Christian,
I am not aware of how to do this. Is this for SmartList Builder? If so, I am thinking maybe eOne can help you with this?
-Victoria
LikeLike
Thanks for the quick reply Victoria!
It is SmartList Builder. I will have to review with them directly. Thanks!
LikeLike
Thank you! It is very Helpful!!! 🙂
LikeLike
Will this work with GP 2013?
LikeLike
Kerry,
Yes, it should. Does it not look like it’s working?
-Victoria
LikeLike
It looks great, thanks much!
LikeLike
Hi Victoria,
I found that making the following changes to this will help produce more accurate results:
Convert the “R.DICTID” value to varchar to ensure a blank value is populated instead “0”
coalesce(CONVERT(varchar(25),R.DICTID),SO.ASI_DICTID,”) Dictionary_ID,
Change the fixed text for the ResType from “Smartlist” to “SmartList Objects” to ensure the column values are uniform
‘SmartList Objects’ ResType
Thanks,
Nick
LikeLike
Hi Victoria,
I posted a request on Partner GP Forums to ask the community how to create a Security SQL view to show which smartlists each user can access…and your friend Mariano Gomez referred me to your super-duper article…This is more than I could have asked for…it’s so wonderfully inclusive…so hats-off to Robert and yourself!
Only issue I’m having is when attempting to add this view as a Smartlist Builder Object. I regularly create SQL views for GP “Company” databases and add them as Smartlist Builder Objects. However, I have not attempted that with a “DYNAMICS” database SQL view until now. I’m logged in as ‘sa’, DYNGRP permissions are set to the SQL view, and there is a security task for smartlist builder permissions allowing me to ‘create Smartlists with SQL tables’ and ‘View Smartlists with SQL Tables. However, when I go to Smartlist Builder SQL Table Security and mark DYNAMICS as the database to use, nothing appears for Tables and Views…it’s completely grayed out? I’ve tried this on my test machine and on a client with same results. Am I missing something?
Thanks again,
Jeff Rowles
L. Kianoff & Associates
LikeLike
Hi Jeff,
Is the checkbox to the left of the DYNAMICS database checked? If not, that would cause what you are seeing.
-Victoria
LikeLike
Thanks for the quick reply Victoria…yes it is.
LikeLike
Jeff,
That’s very strange. I know it works, because we have several SmartLists based on views in the DYNAMICS db and they are working just fine. I just checked on our GP 2010 R2 install and I see all those options when logged in as my regular GP user who is a POWERUSER. Can you try that – logging in as a user with POWERUSER rights – to see if that makes any difference?
-Victoria
LikeLike
I was originally logged in with my user account that has Poweruser rights, also tried it with ‘sa’ and experienced same result. But after going back and forth and trying several things, I figured out what was happening. If I just clicked on the DYNAMICS checkmark nothing would show…but I was not also clicking directly on the database name ‘DYNAMICS’…once I did this, the view appeared!
Thanks again for your help!
Jeff
LikeLike
Victoria,
This is great, thank you for sharing so much with us. When I look at the view I see several records where the Resource Type is blank. What does that mean?
Leslie
LikeLike
Hi Leslie,
Great question! I did a bunch of testing on this and what I am seeing is that there are a number of operations in the SY10700 table that do not have resource details in the SY09400 table. You can see this by running the following against the DYNAMICS database:
SELECT O.*, R.*FROM SY10700 O
LEFT OUTER JOIN SY09400 R
ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE
AND O.SECURITYID = R.SECURITYID
I am doing left joins to make sure as much data as possible is captured in the view and showing any NULLs as blank so if this is used in SmartList it doesn’t return garbled results (not sure if this is still an issue in GP 2010, but it was in GP 10.0).
From the results it looks like anything with a SECRESTYPE of 1, 600,900 or 1000 does not have corresponding details in the SY09400 table and thus no resource descriptions in the view. SECRESTYPE of 1000 is SmartList, so that is added into the view separately, but I don’t know what the others are. I will try to investigate this and will report back if I find anything, but if you have any resources for this information, please let me know.
Thanks!
-Victoria
LikeLike
Hi Victoria,
SECRESTYPE of 1 = Tables, 600 =Series Posting Permissions, 900 = Navigation Lists. The omission of the Series Posting Permissions I believe is worth noting. I am working on a tool that will include all of these resources descriptions, when it is ready for prime time I will let you know.
Cheers!
Terry Blaser
Premier Field Engineer
Microsoft Services
LikeLike
Hi Terry,
Thank you very much for the info! Looking forward to checking out the tool you’re working on.
-Victoria
LikeLike
This one is interesting because I really need to be able to run a report on Series Posting Permissions SECRESTYPE = 600 but have not been able to find the table that stores this information. It’s funny, because it shows up in the report “Security Task Setup Report” as “Series Posting Permissions” ,”General Entry”, “Financial”, Microsoft Dynamics GP” (as an example of someone who has permission to post Journal entries. So that report is looking it up somewhere…… I just wish I could find that table. Maybe I’ll run a SQL Trace.
LikeLike
Any update Terry?
LikeLike
Hi Victoria.
I’ve recently started working with Microsoft Dynamics GP and wanted to “audit” my system to see what users are in the system and what types of security roles each user has. I then want to see if anyone has “incompatible” roles which would allow them to do things that they weren’t supposed to be doing.
The above script seems to be a life saver if it does what I think it does. Will the output of the above script give me the information that I’m looking for?
Also, could you explain it a bit further on how to run the above script? Is it just a cut and paste? Do I need to edit the script before running it?
I look forward to reading your blogs further and look forward to your response and any other advice that you could provide.
Brett
LikeLike
Hi Brett,
The script in this post will give you a lot of data, most likely you will need a lot of GP specific knowledge to make sense of it all. If you’re just starting to work with GP, I would recommend working with someone knowledgeable at your GP Partner organization to help you with this project. Some of the security role and task names are very ambiguous, especially if you are not used to GP terminology yet. For all the information that you can find in books or on the web, nothing can compare to the experience of having worked with GP for something like this.
To answer you question about how to actually use the script – if you have SmartList Builder, you can follow the step in my blog post on How to use a SQL view in SmartList Builder. You should be able to run the script as it is with no changed needed. Also make sure to follow the 6 steps at the beginning of this post, otherwise you will only get partial data returned.
-Victoria
LikeLike
Thanks for such a quick response Victoria.
I understand that the security roles and task names may be ambiguous, do you know of a good document that I’d be able to read which would put them into perspective?
Also, I’m trying to create my own segregation of duties matrix. I want to see which users are assigned which roles and tasks and which may be incompatible with each other. Do you know of an easier way to do this? Does documentation exist somewhere which would make this easier to do?
Finally, do you have a screen shot of the output from the script that we spoke about in my initial post? I’d love to see what the output looks like to determine if I should give it a go.
Thanks again.
Brett
LikeLike
Brett,
There is no documentation that I am aware of that would help you do this by yourself. This is why the help of someone experienced with Dynamics GP is critical for the task you’re undertaking.
Here is a link to a screenshot of the results: https://victoriayudin.files.wordpress.com/2010/07/example-of-security-detail.png. (You can typically click on the image to make it clearer.)
-Victoria
LikeLike
Brett,
There are a couple of 3rd party products available that I believe try to address your concern. Check out Rockton Software’s Auditor/Security Auditor http://www.rocktonsoftware.com. Also FastPath has a similar product http://gofastpath.com
Leslie
LikeLike