List of Access Exceptions (SQL Statement)

The following SQL statement can be used to return a list of all access exceptions.

SELECT

MM405_USER_NAME
Role_Desc
,US653_ROLE_ID
Role_ID
,US653_ELEMENT_TYPE
Element_Type
,FEATUREELEMENTSECTION.MM033_SEC_ID
Section_ID
,FEATUREELEMENTSECTION.MM033_INTERNAL_LABEL
Section_Desc
,FEATUREELEMENTACTION.MM040_TOOLBAR_BUTTON_ID
Button_ID
,FEATUREELEMENTACTION.MM040_BUTTON_NAME
Button_Name
,FEATUREELEMENTCOLUMN.ZZ920_COLUMN_ID
Column_ID
,FEATUREELEMENTCOLUMN.ZZ920_COMMENT
Column_Name
,case when US653_READ = 1 then 'Deny' else 'Grant' end
Grant_Deny

FROM US653_ROLE_ELEMENTS
LEFT JOIN MM033_SECTION_MASTER FEATUREELEMENTSECTION WITH (NOLOCK)
ON US653_ROLE_ELEMENTS.US653_ELEMENT_ID = FEATUREELEMENTSECTION.MM033_SEC_ID
AND US653_ELEMENT_TYPE = 'SC'
LEFT JOIN MM040_TOOLBAR_BUTTON_MASTER FEATUREELEMENTACTION WITH (NOLOCK)
ON US653_ELEMENT_ID = MM040_TOOLBAR_BUTTON_ID
AND US653_ELEMENT_TYPE = 'TB'
LEFT JOIN ZZ920_COLUMNS FEATUREELEMENTCOLUMN WITH (NOLOCK)
ON
US653_ELEMENT_ID = FEATUREELEMENTCOLUMN.ZZ920_COLUMN_KEY
AND US653_ELEMENT_TYPE = 'CO'
LEFT JOIN MM405_USER_MASTER_EXT WITH (NOLOCK)
ON MM405_USER_ID = US653_ROLE_ID
ORDER BY
Role_Desc ASC
, Button_Name ASC

*Content contributed by Tickett Enterprises Limited