List All Users with System Privileges

System Privileges are used for Administrative Tasks. This can be assigned to users directly and via roles also. This can also be assigned to roles.

Reason: find out how many users have DATA ADMIN system privileges or remove CATALOG READ from all the users.

A database user should have CATALOG READ System Privileges to run the query about other users.

How to find List All Users with System Privileges

Please follow the below steps:

1- Connect to System via HANA Studio

2- Open SQL console (Righ click to System and Choose open SQL Console)

3- Execute the below query:

SELECT * from SYS.GRANTED_PRIVILEGES where PRIVILEGE = 'anyPrivilegeName'

It is mandatory to specify Privilege name in where clause of above query.

The below query will show the users who have DATA ADMIN Privileges.

SELECT * FROM SYS.GRANTED_PRIVILEGES WHERE PRIVILEGE = 'DATA ADMIN'

The above screen shows all the users with DATA ADMIN System Privileges. The result screen has the below columns:

Column name	Description
GRANTEE SCHEMA NAME	Schema name which assigned to users.
GRANTEE	User or role that has the privilege
GRANTEE_TYPE	'USER' or 'ROLE'
GRANTOR	User or role that provided the privilege
GRANTOR_TYPE	'USER' or 'ROLE'
OBJECT_TYPE	Type of the privileges like System, object etc.
SCHEMA_NAME	Schema name the object belongs to
OBJECT_NAME	Granted Object name
COLUMN_NAME	Column name
PRIVILEGE	Granted Privileges like Catalog Read, User Admin etc.
IS_GRANTABLE	'TRUE', 'FALSE'
IS_VALID	'TRUE', 'FALSE'

Using the below query, you can find the list of users with ROLE ADMIN privileges. Similar you can use this query for finding system privileges.

eg: SELECT * FROM SYS.GRANTED_PRIVILEGES WHERE PRIVILEGE = 'ROLE ADMIN'

For more details, please check SAP Help Portal