March 12, 2009 
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 19, 2017: Please note that even though this was originally written for GP 10.0, this will also work for GP 2010, GP 2013, GP 2015 and GP 2016.)
~~~~~
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.
Victoria Yudin 
Hi Victoria,
I just tried running this view and I am getting an error stating invalid object name SY10500. We are using GP 15. Would you know why it doesn’t like that table? Thanks,
LikeLike
Hi Katie,
Please try running it against the system database (it is usually called DYNAMICS).
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
This query is great. Do you happen to have a similar one for GP9? Please!!
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
Victoria,
Follow David Musgrave’s instructions to generate the data (lifted from discussions).
Go to Microsoft Dynamics GP >> Tools >> Maintenance Clear Data
Click Display, Click Physical. Select System Series and Insert the Security
Resource Descriptions table.
Click OK and send the report to the screen
Now you can join with the SY09400 table at the SQL level to get names for
forms and reports.
David Musgrave [MSFT]
Escalation Engineer – Microsoft Dynamics GP
Microsoft Dynamics Support – Asia Pacific
Full credit to Mr. Musgrave for this (I couldn’t have made this up).
Tim Foster
LikeLike
Tim,
Wow! That is fantastic! Thank you very much for this. I will publish a new script using this table in a day or two.
-Victoria
LikeLike
Steven, I just posted a new view with all the resource details: https://victoriayudin.com/2009/03/20/sql-view-with-security-resource-details-for-dynamics-gp-10/. Hopefully this is what you’re looking for.
-Victoria
LikeLike
Thankx, Victoria… it is really helpful for us……..:)
LikeLike
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
LikeLike
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
LikeLike
Thank you Victoria!!!
LikeLike