Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Wednesday, January 23, 2013

HowTo: Query to show SAP roles and transaction codes by user

Print Friendly and PDF
Scenario: You have been requested to provide a list of all roles currently assigned to your SAP user accounts, plus the transaction codes that are assigned to each role and user account.

HINT: To be able to do this within SAP, you can use the SAP QuickViewer (SQVI) to create a query and join the required tables. You could then generate a program and then copy it to create your own Z-report.

Using the following Oracle SQL*Plus query at the database level, will allow you to produce a report containing the USERNAME, ROLENAME, TCODE_RANGE_START and TCODE_RANGE_END.

set linesize 500 pagesize 9999 newpage none recsep none
SELECT u.uname USERNAME,
               r.agr_name ROLENAME,
               r.low TCODE_RANGE_START,
               r.high TCODE_RANGE_END
  FROM agr_1251 r,
       (select mandt,
               uname,
               agr_name
          from agr_users) u
 WHERE r.agr_name = u.agr_name
   AND r.mandt = u.mandt
   AND r.mandt = <YOUR CLIENT>
   AND r.object='S_TCODE'
ORDER BY u.uname,r.agr_name,r.low,r.high;


NOTE: You should adjust "<YOUR CLIENT>" to be the client number you wish to check.

You should note that TCODE_RANGE_START and TCODE_RANGE_END could contain wild cards as per the usual methods of providing a range of values to an authorisation object in PFCG.

No comments: