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.
~~~~~
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 http://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.
This query is great. Do you happen to have a similar one for GP9? Please!!
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
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.
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
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
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
Steven, I just posted a new view with all the resource details: http://victoriayudin.com/2009/03/20/sql-view-with-security-resource-details-for-dynamics-gp-10/. Hopefully this is what you’re looking for.
-Victoria
Thankx, Victoria… it is really helpful for us……..:)