I think this is the origial (or close) script I got from Victoria
USE [DYNAMICS]
GO
/****** Object: View [dbo].[view_Security_and_SL_Details] Script Date: 10/09/2010 12:31:45 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[view_Security_and_SL_Details]'))
DROP VIEW [dbo].[view_Security_and_SL_Details]
GO
USE [DYNAMICS]
GO
/****** Object: View [dbo].[view_Security_and_SL_Details] Script Date: 10/09/2010 12:31:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[view_Security_and_SL_Details]
AS
/*******************************************************************
view_Security_and_SL_Details
Create this in the DYNAMICS database
Created May 13 2010 by Victoria Yudin - Flexible Solutions, Inc.
and Robert Cavill - Emeco
For updates see victoriayudin.com/gp-reports
Shows all security roles, tasks and detailed resource descriptions
including SmartList by user by company for GP 10.0 and GP 2010
*******************************************************************/
SELECT DISTINCT
S.USERID [User_ID],
S.CMPANYID Company_ID,
C.CMPNYNAM Company_Name,
S.SECURITYROLEID Security_Role_ID,
coalesce(T.SECURITYTASKID,'') Security_Task_ID,
coalesce(TM.SECURITYTASKNAME,'') Security_Task_Name,
coalesce(TM.SECURITYTASKDESC,'') Security_Task_Description,
coalesce(R.DICTID,SO.ASI_DICTID,'') Dictionary_ID,
coalesce(R.PRODNAME,'') Product_Name,
coalesce(R.TYPESTR,SO.ResType,'') Resource_Type,
coalesce(R.DSPLNAME,SO.SmartlistObject,'') Resource_Display_Name,
coalesce(R.RESTECHNAME,'') Resource_Technical_Name,
coalesce(R.Series_Name,'') Resource_Series
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
LEFT OUTER JOIN -- smartlist objects
(SELECT SECURITYTASKID, SECURITYID, DICTID, SECRESTYPE,
ASI_DICTID, SL_OBJID, SmartlistObject,
'Smartlist' ResType
FROM
(SELECT SECURITYTASKID, SECURITYID, DICTID, SECRESTYPE,
SECURITYID / 65536 ASI_DICTID, SECURITYID % 65536 SL_OBJID
FROM SY10700
WHERE SECRESTYPE = 1000 AND DICTID = 1493) ST
JOIN
(SELECT coalesce(TRANSVAL, ASI_Favorite_Name) SmartlistObject,
ASI_Favorite_Dict_ID, ASI_Favorite_Type
FROM ASIEXP81 F
LEFT JOIN
ASITAB30 A
ON F.ASI_Favorite_Name = A.UNTRSVAL
AND A.Language_ID = 0
WHERE ASI_Favorite_Save_Level = 0) SM
ON ASI_DICTID = ASI_Favorite_Dict_ID
AND SL_OBJID = ASI_Favorite_Type) SO
ON SO.DICTID = O.DICTID AND O.SECRESTYPE = SO.SECRESTYPE
AND O.SECURITYID = SO.SECURITYID
/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
