User-Role Assignment details in MDM 7.1

[former_member298408]

Hi,

Can anyone tell me how can I get a list of all the Users and Roles assigned to them in MDM 7.1

[Former Member · Accepted solution]

Hi Aditi,

You would need to extract MDM users from Database directly.

You need to contact your Database administrator and ask them to create join on Users and 1 other table(don't remember the name) in database directly and it will give you user list along with roles assigned to them.

You can also check below link which gives you the method to select all users for a repository, similarly you can check for the roles assignment as well:

http://scn.sap.com/thread/3263776

Below thread will give you an idea about how to check various tables and their details from Database:

http://scn.sap.com/thread/3199205

Please check and let me know if your issue is resolved by this.

Thanks and Regards,

Ankush Bhardwaj

[former_member298408 · Accepted solution]

Hi Ankush,

Thanks a ton for your reply, that pretty much solved my issue.

But I am not good at writing SQL Queries.

I have three tables with me now : A2I_CM_USERS, it gives list of User IDs, User Names and Full Names.

A2I_CM_ROLES, it gives list of Role Id and Role Name and A2I_CM_USER_ROLES, it gives list of User IDs and Role IDs.

Now my Requirement is to get list with Fields User Names, Full Names and Role Names.

Is it possible for you to tell me how to write the SQL Query in this case.

[Former Member · Accepted solution]

Hi Aditi,

Need one information, user names would be unique values right?

And i am also not good at SQL..

But i think a little SQL program would solve the issue which would include creating one temporary table having User ID, User Name, Full Name and Role Name.

Then a simple select query to select all the contents of A2i_CM_USERS table into this temp table.

Then a select query like

Select Role Name from A2i_CM_ROLES where Role ID = (Select Role ID from A2i_CM_USER_ROLES where User ID = A2i_CM_USER.User ID)

This could be an insert query as well which would insert the Role Name into temp table based upon the User ID for each row of temp table.

You need to tweak on these lines itself. May be you can ask for help from some person in your team who knows SQL. Please let me know the final query which solved your issue.

Thanks and Regards,

Ankush Bhardwaj

[Former Member · Accepted solution]

Hi Aditi,

Another simple way would be to extract the contents of these 3 tables into Excel files.

Then create VLOOKUPs on various sheets to get the desired data. Please let me know if you need help in this regard, i might be able to help you here.

Regards,

Ankush Bhardwaj

[former_member298408 · Accepted solution]

Hi Ankush,

I got the data with query: select a.USERNAME,b.ROLENAME from <repository>_M000.A2I_CM_USERS a,<repository>_M000.A2I_CM_ROLES b,<repository>_M000.A2I_CM_USER_ROLES c where a.USERID = c.USERID and b.ROLEID = c.ROLEID

But the trouble is on Database Level it saves data in TXT format and it's a mammoth of task to convert it to Excel format.

Are you aware of anyway through which we can save data in database in excel format.

[Former Member · Accepted solution]

Hi Aditi,

Database might be saving the data in text format by using some delimiter to separate two columns.

You can open this .txt file in Excel and then proceed. Easy way to check this is just select all the contents of .txt file and paste it in Excel file. It should give you better view. Kindly check and let me know.

Thanks and Regards,

Ankush Bhardwaj

[former_member298408 · Accepted solution]

Yeah...I already did that...was just checking if there is anyway to save it directly in excel format.

We have huge data, so manual formatting takes around 20-30 minutes, wanted to save that as well.

Anyway, many thanks for your help.