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.


-- 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)


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';

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Copyright © 2024. Privacy Policy