Kevin,
Thank you much for your investigation on the series posting permission. It helped A TON.
It resulted in this ...... (note this will only work on MSSQL due to temp tables. You can re-write it using something else if you desire.)
* all tasks being used by any roles that are currently being used by any person in your company and the access they hold *
Disclaimer --- this has been tested on limited data and I cannot be held responsible if it's not 100% accurate. ----
I removed some of your property-specific info from your part of the script.
USE DYNAMICS;
-- users and their roles in each company --
SELECT
S.USERID AS UserID,
S.CMPANYID AS CompanyID,
C.CMPNYNAM AS CompanyName,
S.SecurityRoleID AS SecurityRole,
SU.CREATDDT AS CreationDate
INTO #curuserroles
FROM SY10500 AS S LEFT OUTER JOIN
SY01400 AS SU ON S.USERID = SU.USERID LEFT OUTER JOIN
SY01500 AS C ON S.CMPANYID = C.CMPANYID
where
C.CMPNYNAM not like '%TEST%' AND
C.CMPNYNAM not like '%Fabrikam%' AND
--exclude user if inactive
SU.Userstatus <> 2;
-- Currently Used Roles --
select distinct SecurityRole
into #usedroles
from #curuserroles;
/* -- Currently used Roles and their Tasks --
SELECT T.SecurityRoleID,
T.SECURITYTASKID
into #roleswithtasks
FROM
SY10600 T -- tasks in roles
where T.SecurityRoleID in (select SecurityRole from #usedroles)
*/
-- Task IDs belonging to used roles --
SELECT DISTINCT T.SECURITYTASKID
into #usedtasks
FROM
SY10600 T -- tasks in roles
where T.SecurityRoleID in (select SecurityRole from #usedroles);
-- Task Access for each Task used ---
--declare CTEs for Readability and Organization
WITH
SMARTLIST_CTE (SECURITYID, DICTID, SECRESTYPE, ASI_DICTID, SL_OBJID, SmartlistObject, ResType)
AS
(
SELECT SECURITYID, DICTID, SECRESTYPE, ASI_DICTID, SL_OBJID, SmartlistObject,'Smartlist' ResType
FROM
(
SELECT SECURITYID, DICTID, SECRESTYPE, SECURITYID / 65536 ASI_DICTID, SECURITYID % 65536 SL_OBJID
FROM SY10700 WHERE SECRESTYPE = 1000 AND DICTID = 1493
) AS 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
) AS SM
ON ST.ASI_DICTID = SM.ASI_Favorite_Dict_ID AND ST.SL_OBJID = SM.ASI_Favorite_Type
)
,
--series posting resources, the SY09400 table does not contain these - they are within the company.
POSTING_PERM_CTE (DICTID, SECURITYID, SECRESTYPE, Series, ResourceName, TRXSOURC)
AS
(
SELECT DISTINCT
0 as DICTID
,CONVERT(VARCHAR(1), SERIES) + CASE WHEN LEN(SEQNUMBR) = 1 THEN '0' ELSE '' END + CONVERT(VARCHAR(2), SEQNUMBR) as SECURITYID
,'600' as SECRESTYPE
,CASE SERIES
WHEN 1 THEN 'All'
WHEN 2 THEN 'Financial'
WHEN 3 THEN 'Sales'
WHEN 4 THEN 'Purchasing'
WHEN 5 THEN 'Inventory'
WHEN 6 THEN 'Payroll'
WHEN 7 THEN 'Project'
WHEN 10 THEN '3rd Party'
ELSE 'Unknown'
END as [Series]
,'Series Posting Permissions' as [ResourceName]
,TRXSOURC
FROM TWO..SY01000 --any company works here, but it has to be a company db.
WHERE SEQNUMBR <> 0
)
SELECT DISTINCT
coalesce(TM.SECURITYTASKID,'') Security_Task_ID
,coalesce(R.PRODNAME,'') Product_Name
,coalesce(R.TYPESTR,SO.ResType,'') Resource_Type
,coalesce(R.DSPLNAME,SO.SmartlistObject,'') Resource_Display_Name
,coalesce(NULLIF(ISNULL(R.Series_Name, ''), ''),PO.[Series],R.[PRODNAME],'') as [Resource Series]
,coalesce(R.DICTID,SO.ASI_DICTID,PO.DICTID,'') [Dictionary ID],
,CASE O.SECRESTYPE
WHEN 1 THEN 'Files'
WHEN 2 THEN 'Window'
WHEN 23 THEN 'Report'
WHEN 400 THEN 'Document Access'
WHEN 500 THEN 'Customization Tools'
WHEN 600 THEN 'Series Posting Permissions'
WHEN 700 THEN 'Integration Manager'
WHEN 800 THEN 'Letters'
WHEN 900 THEN 'Navigation Lists'
WHEN 1000 THEN 'Smartlist'
WHEN 3830 THEN 'SLB Window'
ELSE 'Unknown'
END as [Resource Type]
,RTRIM(coalesce(R.DSPLNAME,SO.SmartlistObject,PO.TRXSOURC,'')) [Resource Display Name]
FROM SY09000 AS TM -- tasks master
LEFT OUTER JOIN SY10700 AS O -- operations in tasks
ON TM.SECURITYTASKID = O.SECURITYTASKID
LEFT OUTER JOIN SY09400 AS R -- resource descriptions
ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYID
LEFT OUTER JOIN SMARTLIST_CTE AS SO ON SO.DICTID = O.DICTID AND O.SECRESTYPE = SO.SECRESTYPE
AND O.SECURITYID = SO.SECURITYID
LEFT OUTER JOIN POSTING_PERM_CTE as PO ON O.SECRESTYPE = PO.SECRESTYPE AND O.SECURITYID = PO.SECURITYID
where TM.SecurityTaskID in (select SECURITYTASKID from #usedtasks)
** you will want to drop the temp tables after you're finished querying from them, I don't in the script because I use them for other things once this is finished **
I post this to hopefully help someone else make use of the knowledge spread across the web.
