we do have a larger SAP environment connected to our Q1IM system. The whole environment is synchronized down to the transaction level. We now need to determine which SAP roles contain which transactions and vice versa. I did came up with the following query:
Select t.Ident_SAPTransaction, t.Description, g.GroupName, g.Description
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 g on g.UID_SAPGroup = sghaoe.UID_SAPGroup
Does that look correct?
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 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
Thanks for your feedback Steffen.
Now it's "only" some performance tuning to get that running within ITShop. :-)
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?
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.
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.:
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.
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?