Determine the data relation between SAPGroup and SAPTransaction

Hi Carsten,

with your statement you will get the assignments of the transactions to the PROFILES.

If you need the assignments to the ROLES you need 2 additional joins, e.g.:

Select t.Ident_SAPTransaction, t.Description, gp.GroupName as 'Profile', gr.GroupName as 'Role'

from SAPTransaction t

join SAPTransactionHasSAPAuthObject sthsao on t.UID_SAPTransaction = sthsao.UID_SAPTransaction

join SAPGroupHasAuthObjectElem sghaoe on sghaoe.UID_SAPAuthObject = sthsao.UID_SAPAuthObject

join SAPGroup gp /*profile*/ on gp.UID_SAPGroup = sghaoe.UID_SAPGroup

join SAPGroupInSAPGroup gig on gp.UID_SAPGroup = gig.UID_SAPGroupChild

join SAPGroup gr /*role*/ on gig.UID_SAPGroupParent = gr.UID_SAPGroup

Best regards,

Steffen

Thanks for your feedback Steffen.

Now it's "only" some performance tuning to get that running within ITShop. :-)

Cheers

Carsten

Hi Steffen

It's me again. After implementing the query it seems like we do get incorrect results when looking for SAP-Roles having a certain transaction. The problem must be within the query, cause when i'm using one of my examples, navigate to the SAP profile that is assigned and take a look at the SAP Authorizations in the Manager, i do not see the Transaction Code i was looking for in the Query. Also our results do not comply with the search results of the SAP Admins of our customer. Any idea on that? Or would it be more sufficient to open a service request with Quest Support for such kind of assistance?

Thanks

Carsten

Hi Carsten,

I don't understand the data structure for this (would need a little bit time - or more ).

But I have a working solution for you.

We already have a SQL function named "VI_F_SAP_TransactionForGroup" in the db.

It needs one parameter: An UID_SAPGroup, this must be a SAP profile.

If you want to know the assigned SAP transactions to one given SAP profile you can use this one, e.g.:

select * from dbo.VI_F_SAP_TransactionForGroup('14a76512-241e-4622-936f-10cd958e4683')

To get a complete list for all SAP profiles I implemented a wrapper for this function.

(attached)

You can call it with such a statement:

select * from dbo.SE_F_SAP_TransactionForGroup()

It returns the assignment of the SAP transactions to all SAP profiles.

If you want to know the assignment to the SAP roles (this was the initial demand) you can use this table function for additional joins, e.g.:

select

    phf.ident_saptransaction,

    gsr.GroupName as 'Single Role'

from dbo.SE_f_sap_transactionforgroup() phf /*profile has function*/

join SAPGroupInSAPGroup gig on phf.UID_SAPGroup = gig.UID_SAPGroupChild

join SAPGroup gsr /*single role*/ on gig.UID_SAPGroupParent = gsr.UID_SAPGroup

I hope this solves your problem.

Best regards,

Steffen

Our problem is exactly the other way round. :-)

We're trying to implement a functionality into the ITShop where the requester can select the SAP System and Client, enter a Transaction Code (e.g. F-52 or MF52) or search for Transaction Codes using parts of the Transaction Code. Based out of those parameters we need to bring up all SAP Roles having GroupFlag = 1, having this transaction. All found SAP roles having an AccProduct will get shown in the ITShop and the user can select the appropriate role.

Any ideas or would something like that be worth a service request?

Thanks

Carsten

I think it's possible with my last solution.

I add a where clause to the statement mentioned above to get all roles for a given transaction:

select

    phf.ident_saptransaction,

    gsr.GroupName as 'Single Role'

from dbo.SE_f_sap_transactionforgroup() phf /*profile has function*/

join SAPGroupInSAPGroup gig on phf.UID_SAPGroup = gig.UID_SAPGroupChild

join SAPGroup gsr /*single role*/ on gig.UID_SAPGroupParent = gsr.UID_SAPGroup

where phf.UID_saptransaction = '<the uid of the selected transaction>'