SAP HANA – insufficient privilege: Cannot grant activated roles

In last time I created a new user as a copy of an existing one. I use short SQL queries for this purpose. For example, below query give me ready output for granting roles to NEW_USER exactly the same like TEMPLATE_USER.

-- granted roles:
 select 'grant "' || role_name || '" to NEW_USER;' from SYS.GRANTED_ROLES where grantee='TEMPLATE_USER';

This is good for standard database role but the problem arises when You using predefined roles, e.g. sap.hana.xs.* or sap.hana.replay.*, etc.

Example:

-- You try grant role:
grant "sap.hana.replay.roles::Capture" to NEW_USER';

-- Output:
Could not execute 'grant "sap.hana.replay.roles::Capture" to NEW_USER'
SAP DBTech JDBC: [258]: insufficient privilege: Cannot grant activated roles: line 1 col 7 (at pos 6)

Solution

To eliminate above query error You should execute:

call grant_activated_role ('sap.hana.replay.roles::Capture', 'NEW_USER');

Here You have helpful query, which will give You the ability to grant permissions like sap.hana* to a NEW_USER based on TEMPLATE_USER:

select 'call grant_activated_role (''' || role_name || ''', ''NEW_USER'');' from SYS.GRANTED_ROLES where role_name like 'sap.hana%' and grantee='TEMPLATE_USER';