cancel
Showing results for 
Search instead for 
Did you mean: 

SYSTEM Table to get all users and roles assigned to that users.

Ajinkya_Jadhav
Participant
0 Kudos

Hello Experts,

Do we have any SYSTEM table which helps us to get list of users and roles assigned to the user.

Thank You.

Best Regards,

Ajinkya Jadhav

Ajinkya_Jadhav
Participant
0 Kudos

HANA Version: 2.00.037.03.1574689900

Error message :

Could not execute 'SELECT * FROM EFFECTIVE_ROLES' in 5 ms 483 µs . 
SAP DBTech JDBC: [500]: predicates are required in a where clause: M_EFFECTIVE_ROLES_ needs predicates on columns (connected by AND if more than one): an equal predicate on USER_NAME 

Accepted Solutions (0)

Answers (1)

Answers (1)

pfefferf
Active Contributor

Have you checked following system views:

Ajinkya_Jadhav
Participant
0 Kudos

I am getting error while executing

SELECT * FROM EFFECTIVE_ROLES

How should it be, I am doing it in wrong way?

pfefferf
Active Contributor
0 Kudos

What HANA version you are using?

Ajinkya_Jadhav
Participant
0 Kudos

We have HANA 2.0 SPS 03 102

pfefferf
Active Contributor
0 Kudos

In that version you have that system view. And what error do you get?

Ajinkya_Jadhav
Participant
0 Kudos

HANA Version: 2.00.037.03.1574689900

Error message :

Could not execute 'SELECT * FROM EFFECTIVE_ROLES'in5ms483µs .SAP DBTech JDBC:[500]: predicates are required in a where clause: M_EFFECTIVE_ROLES_ needs predicates oncolumns(connected byANDif more than one): an equal predicate on USER_NAME 
pfefferf
Active Contributor
0 Kudos

If you read the error message carefully, than you know what to do ... you have to restrict the query with a where condition on user_name (e.g. user_name = 'SYSTEM').

Ajinkya_Jadhav
Participant
0 Kudos

Yes that is working but the issue is I need the list of all users at a time. I tried with creating join with USERS table as well but it does not worked.

Following is the query which fulfill my requirement.

SELECT * FROM EFFECTIVE_ROLES
WHERE
ROLE_NAME = 'MONITORING'
AND
USER_NAME IN ( 
SELECT USER_NAME 
FROM USERS
WHERE USER_DEACTIVATED = 'FALSE');



SELECT * FROM EFFECTIVE_ROLES
WHERE
ROLE_NAME = 'MONITORING'
AND
USER_NAME IN ( 
SELECT TOP 2 USER_NAME 
FROM USERS
WHERE USER_DEACTIVATED = 'FALSE');

Below query is working fine but when I select for all users it is thowing before mentioned error.

select * FROM EFFECTIVE_ROLES
WHERE
ROLE_NAME = 'MONITORING'
AND
USER_NAME IN ( 'EUVVQ', 'EUVVP')
pfefferf
Active Contributor
0 Kudos

As a workaround you can created your own table function which first determines the users and than determines the role information per user (appending the result to an out table).