Find Unused Permission Lists

By Chris Malek | Wed, Oct 13, 2010

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 ;
Article Categories
Author Info
Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris
Product
PeopleSoft Simple Web Services (SWS)
Do you need a quick way to create web services?

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Book
Integration Broker - The Missing Manual

I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.