Search This Blog

Monday, October 12, 2009

Find Unused Permission Lists

This query should find unused permission lists that could be deleted.

SELECT * FROM PSCLASSDEFN A
WHERE NOT EXISTS ( SELECT 'X' FROM PSUSERCLASSVW B WHERE A.CLASSID = B.CLASSID )
AND NOT EXISTS (SELECT 'X' FROM PSOPRDEFN B WHERE A.CLASSID = B.OPRCLASS)
AND NOT EXISTS (SELECT 'X' FROM PSOPRDEFN B WHERE A.CLASSID = B.PRCSPRFLCLS)
AND NOT EXISTS (SELECT 'X' FROM PSOPRDEFN B WHERE A.CLASSID = B.DEFAULTNAVHP)
AND NOT EXISTS (SELECT 'X' FROM PSOPRDEFN B WHERE A.CLASSID = B.ROWSECCLASS)
AND A.LASTUPDOPRID <> 'PPLSOFT'
ORDER BY CLASSID ;

No comments:

Post a Comment