SQL view with security and SmartList details in GP

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.

27 Responses to “SQL view with security and SmartList details in GP”

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.

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?

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?

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!

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:

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.

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.

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.

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.

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.

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.

[…] is a great SQL View on MPV Victoria Yudin’s blog site called “SQL View with Security and SmartList details in GP”. Be sure to read her entire post as the SQL View is dependent on the Security Resource […]