SQL view with security resource details for Dynamics GP


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:

  1. Go to Microsoft Dynamics GP > Maintenance > Clear Data
  2. Click Display on the toolbar and click Physical
  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
  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
  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. (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.

30 Responses to “SQL view with security resource details for Dynamics GP”

  1. 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?

    Like

  2. 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?

    Like

    • Hi JoAnn,

      Have you tried going through all the steps listed in this blog post?

      -Victoria

      Like

      • 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….

        Like

        • 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

          Like

  3. I get this message >>> A fatal scripting error occurred.
    Incorrect syntax was encountered while parsing GO.

    Like

    • 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

      Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

    • 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

      Like

    • Hi Sebastien,

      I heard back from Microsoft that there is no way to see this information without a Dexterity customization. 😦

      -Victoria

      Like

      • 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

        Like

        • 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

          Like

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

    Like

    • 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

      Like

      • 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

        Like

        • Hi Santosh,

          Have you tried repopulating the data in the SY09400 table by going through the 6 steps I outlined?

          -Victoria

          Like

          • Hi Victoria,

            Yes I followed the same step which you have mentioned.

            Regards,
            Santosh

            Like

            • Santosh,

              And your SY09400 table, if you look at it in SQL, has data?

              -Victoria

              Like

              • 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

                Like

                • 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

                  Like

                  • 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

                    Like

                    • 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

                      Like

  8. Do you know of any way to include Smartlist objects in the SY09400 table?

    Like

    • 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

      Like

Trackbacks/Pingbacks

  1. Weekly Dynamic: Create a Super User | DynamicAccounting.net - April 28, 2014

    […] 1) Clear the SY09400 table. Victoria Yudin has specifics. […]

    Like

  2. SQL View with Security Resource Details for GP 10 - DynamicAccounting.net - March 25, 2009

    […] Yudin continues to put out great security resources for Dynamics GP 10. This time it's a SQL View showing security resource details. Published: Tuesday, March 24, 2009, 11:00 […]

    Like

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

%d bloggers like this: