SQL view to show security roles and tasks in Dynamics GP


Dynamics GP 10.0 has brought about a drastic change in GP security. Not only is the security pessimistic now, so by default no permissions are granted, but setting up and administering security is quite different from what many of us are used to after working with previous versions of GP for many years. And, of course, reporting on security requires a completely new set of tables. Below is a view to show the security roles and tasks assigned to each user in each GP company.  And for a lot more information about GP security, take a look at David Musgrave’s Microsoft Dynamics GP Application Level Security Series and the Support Debugging Tool.

(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_Roles_and_Tasks
AS

/**
view_Security_Roles_and_Tasks
Created Mar 12 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Shows all security roles and tasks by user by company
**/

SELECT	S.USERID UserID, S.CMPANYID CompanyID,
	C.CMPNYNAM CompanyName, S.SecurityRoleID,
	coalesce(T.SECURITYTASKID,'') SecurityTaskID,
	coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName,
	coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription

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 

/** 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_Roles_and_Tasks 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.

22 Responses to “SQL view to show security roles and tasks in Dynamics GP”

  1. Victoria is there a way to run a historical report of all users/tasks/roles for audit purposes? E.g., I need to compare the end of 2014 list to the current list in order to identify any changes in access during the current year. This would be for GP 2013 and SQL Server 2012 database.

    Like

    • Hi Mitchell,

      Dynamics GP does not store any history of security changes. So unless you have a backup from the end of 2014 that you can restore as another database, I don’t see how you would be able to create a report like this.

      -Victoria

      Like

  2. This query is great. Do you happen to have a similar one for GP9? Please!!

    Like

    • Brooksj,

      I am not aware of anything like this available or possible for GP 9.0 or earlier versions. I would recommend posting a question on the Dynamics GP Newsgroup to see if anyone has something that would help, but I don’t think that there is anywhere in the database where a list of the security objects appears with anything other than code numbers.

      -Victoria

      Like

  3. Thank you for this view. I saw a similar view a while ago that included individual operations – and therefore what tasks those operations were assigned to – from the SY09400 table. Have you looked at this table at all? Smartlist objects were not included so I thought I would ask.

    Like

    • Steven,

      I am not seeing any data in the SY09400 in my installation and a few others that I just checked. Nor do I see any information on that table in the SDK, unless there is a section in there on security that I am totally missing. So I am not sure what should be in there. Do you have data in that table? What does it look like?

      -Victoria

      Like

  4. Thankx, Victoria… it is really helpful for us……..:)

    Like

    • Hi Victoria,

      As always, thank you for the great information you provide for us. Question… I need to copy security roles and tasks from a DEV/QA environment to a PROD environment. The issue I see is that these updated roles and tasks need to be removed in prod before the new ones get implemented. Do you know of any scripts out there that would help me accomplish this? Please note that only certain roles and tasks would be updated… not all of them.

      Much thanks,
      Donald Wisch

      Like

      • Hi Donald,

        There is likely a way to do this with scripts, but I have not done this before, nor have I seen any scripts like this out there. If you have a lot of this going on, it may be worthwhile to check with Microsoft support to see if they can do this for you. Or they might give you some guidance to be able to create the scripts yourself.

        -Victoria

        Like

Trackbacks/Pingbacks

  1. Sql User Security Roles | Sufferband - June 27, 2016

    […] SQL view to show security roles and tasks in Dynamics GP … – Dynamics GP 10.0 has brought about a drastic change in GP security. Not only is the security pessimistic now, so by default no permissions are granted, but setting up … […]

    Like

  2. DynamicAccounting.net | SQL View to Show Dynamics GP SecuritySQL View to Show Dynamics GP Security - DynamicAccounting.net - April 25, 2016

    […] on March 16, 2009 by Mark Polino Ooooooh! Victoria Yudin has SQL code to show security roles and tasks in Dynamics GP 10. […]

    Like

  3. Security Tips in Microsoft Dynamics GP – Victoria Yudin - Microsoft Dynamics GP Community - November 16, 2015

    […] Victoria Yudin has a SQL view to show security roles and tasks in Dynamics GP […]

    Like

  4. DynamicAccounting.net | Security Tips in Microsoft Dynamics GP – Victoria YudinSecurity Tips in Microsoft Dynamics GP - Victoria Yudin - DynamicAccounting.net - November 16, 2015

    […] Victoria Yudin has a SQL view to show security roles and tasks in Dynamics GP […]

    Like

  5. Victoria Yudin - September 28, 2010

    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…

    Like

  6. SQL view with security and SmartList details in GP « Victoria Yudin - May 16, 2010

    […] Cavill from Emeco in Australia has sent me an amazing update to my Security Roles and Tasks in GP 10 SQL script that includes SmartList objects. I have tested this script with both GP 10.0 and GP […]

    Like

  7. SQL view with security and SmartList details in GP - Victoria Yudin - May 13, 2010

    […] Cavill from Emeco in Australia has sent me an amazing update to my Security Roles and Tasks in GP 10 SQL script that includes SmartList objects. I have tested this script with both GP 10.0 and GP […]

    Like

  8. SQL view with security resource details for Dynamics GP 10 - Victoria Yudin - March 20, 2009

    […] view with security resource details for Dynamics GP 10 As a corollary to my view showing the Dynamics GP 10.0 security roles and tasks assigned to users, I have created another view that adds the security resource details. Thank you very much to Tim […]

    Like

  9. SQL View to Show Dynamics GP Security - DynamicAccounting.net - March 16, 2009

    […] View to Show Dynamics GP Security Ooooooh! Victoria Yudin has SQL code to show security roles and tasks in Dynamics GP 10. Suweeet! Published: Monday, March 16, 2009, 04: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: