Dynamics GP - Security Permission View - UserAccess Which user has access to what Roles, Tasks, and Windows?

Original Post
https://victoriayudin.com/2009/03/12/sql-view-to-show-security-roles-and-tasks-in-dynamics-gp-10/

Modified View
----------------------------------------------------------------------------------------------------------------
/****** Object:  View [dbo].[BI_UserAccess]    Script Date: 08/28/2017 01:52:39 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_UserAccess]
AS
SELECT DISTINCT
                         S.USERID AS User_ID, S.CMPANYID AS Company_ID, C.CMPNYNAM AS Company_Name, S.SECURITYROLEID AS Security_Role_ID, COALESCE (T.SECURITYTASKID, '') AS Security_Task_ID,
                         COALESCE (TM.SECURITYTASKNAME, '') AS Security_Task_Name, COALESCE (TM.SECURITYTASKDESC, '') AS Security_Task_Description, COALESCE (R.DICTID, SO.ASI_DICTID, '') AS Dictionary_ID,
                         COALESCE (R.PRODNAME, '') AS Product_Name, COALESCE (R.TYPESTR, SO.ResType, '') AS Resource_Type, COALESCE (R.DSPLNAME, SO.SmartlistObject, '') AS Resource_Display_Name,
                         COALESCE (R.RESTECHNAME, '') AS Resource_Technical_Name, COALESCE (R.Series_Name, '') AS Resource_Series
FROM            DYNAMICS.dbo.SY10500 AS S LEFT OUTER JOIN
                         DYNAMICS.dbo.SY01500 AS C ON S.CMPANYID = C.CMPANYID LEFT OUTER JOIN
                         DYNAMICS.dbo.SY10600 AS T ON S.SECURITYROLEID = T.SECURITYROLEID LEFT OUTER JOIN
                         DYNAMICS.dbo.SY09000 AS TM ON T.SECURITYTASKID = TM.SECURITYTASKID LEFT OUTER JOIN
                         DYNAMICS.dbo.SY10700 AS O ON T.SECURITYTASKID = O.SECURITYTASKID LEFT OUTER JOIN
                         DYNAMICS.dbo.SY09400 AS R ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYID LEFT OUTER JOIN
                             (SELECT        ST.SECURITYTASKID, ST.SECURITYID, ST.DICTID, ST.SECRESTYPE, ST.ASI_DICTID, ST.SL_OBJID, SM.SmartlistObject, 'Smartlist' AS ResType
                               FROM            (SELECT        SECURITYTASKID, SECURITYID, DICTID, SECRESTYPE, SECURITYID / 65536 AS ASI_DICTID, SECURITYID % 65536 AS SL_OBJID
                                                         FROM            DYNAMICS.dbo.SY10700
                                                         WHERE        (SECRESTYPE = 1000) AND (DICTID = 1493)) AS ST INNER JOIN
                                                             (SELECT        COALESCE (A.TRANSVAL, F.ASI_Favorite_Name) AS SmartlistObject, F.ASI_Favorite_Dict_ID, F.ASI_Favorite_Type
                                                               FROM            DYNAMICS.dbo.ASIEXP81 AS F LEFT OUTER JOIN
                                                                                         DYNAMICS.dbo.ASITAB30 AS A ON F.ASI_Favorite_Name = A.UNTRSVAL AND A.Language_ID = 0
                                                               WHERE        (F.ASI_Favorite_Save_Level = 0)) AS SM ON ST.ASI_DICTID = SM.ASI_Favorite_Dict_ID AND ST.SL_OBJID = SM.ASI_Favorite_Type) AS SO ON SO.DICTID = O.DICTID AND
                         O.SECRESTYPE = SO.SECRESTYPE AND O.SECURITYID = SO.SECURITYID

GO


-------------------------------------------------------------------------------