You are running a DMO upgrade and migrate project. In early phases the DMO asks to provide the HANA connection details then password for SAP<SID> user and DBACOCKPIT. DMO uses this information to create the users and schema and then build up the hdbuserstore entries DEFAULT and SUPER. At a later point in time for some reason (this shouldn’t happen – but reality is much stronger than fiction 🙂 ) all is lost and there’s no more DBACOCKPIT and SAP<SID>. So now you create the two users manually from HANA studio. DMO is happy and upgrade continues, BUT the two users do not have the proper roles and privileges. From a project lesson learned, below you can find all the needed roles to be created and granted to both users.
In a out-of-the-box system you have:
DBACOCKPIT
select grantee,object_type,schema_name,privilege from SYS.GRANTED_PRIVILEGES where grantee = ‘DBACOCKPIT’;
| GRANTEE | OBJECT_TYPE | SCHEMA_NAME | PRIVILEGE |
| DBACOCKPIT | SCHEMA | DBACOCKPIT | CREATE ANY |
select grantee,role_name from SYS.GRANTED_roles where grantee = ‘DBACOCKPIT’;
| GRANTEE | ROLE_NAME |
| DBACOCKPIT | PUBLIC |
| DBACOCKPIT | AFL__SYS_AFL_ERPA_EXECUTE |
| DBACOCKPIT | DBA_COCKPIT |
SAPSID
select grantee,object_type,schema_name,privilege from SYS.GRANTED_PRIVILEGES where grantee = ‘SAPMR0’;
| GRANTEE | OBJECT_TYPE | SCHEMA_NAME | PRIVILEGE |
| SAPMR0 | SCHEMA | SAPMR0 | CREATE ANY |
select grantee,role_name from SYS.GRANTED_roles where grantee = ‘SAPMR0’;
| GRANTEE | ROLE_NAME |
| SAPMR0 | PUBLIC |
| SAPMR0 | AFL__SYS_AFL_ERPA_EXECUTE |
| SAPMR0 | DBA_COCKPIT |
| SAPMR0 | USER |
| SAPMR0 | TABLE_REDISTRIBUTION |
| SAPMR0 | ABAP_READ |
| SAPMR0 | ABAP_ADMIN |
| SAPMR0 | ABAP_DEV |
| SAPMR0 | ABAP_SYS_REPO |
Here comes the funny part, to create all of these roles. To keep a long story short, see below the statements:
CREATE ROLE ABAP_ADMIN;
GRANT ROLE ADMIN TO ABAP_ADMIN;
GRANT USER ADMIN TO ABAP_ADMIN;
GRANT CATALOG READ TO ABAP_ADMIN;
GRANT SELECT ON SCHEMA SAP<SID> TO ABAP_ADMIN;
GRANT EXECUTE ON _SYS_REPO.GRANT_ACTIVATED_ROLE TO ABAP_ADMIN;
GRANT EXECUTE ON _SYS_REPO.REVOKE_ACTIVATED_ROLE TO ABAP_ADMIN;
CREATE ROLE ABAP_DEV;
GRANT CATALOG READ TO ABAP_DEV;
GRANT DEVELOPMENT TO ABAP_DEV;
GRANT SELECT ON SCHEMA SAP<SID> TO ABAP_DEV;
CREATE ROLE ABAP_READ;
GRANT SELECT ON SCHEMA SAP<SID> TO ABAP_READ;
CREATE ROLE ABAP_SYS_REPO;
GRANT SELECT ON SCHEMA SAP<SID> TO ABAP_SYS_REPO;
CREATE ROLE TABLE_REDISTRIBUTION;
GRANT SERVICE ADMIN TO TABLE_REDISTRIBUTION;
GRANT RESOURCE ADMIN TO TABLE_REDISTRIBUTION;
GRANT SELECT ON _SYS_REPO.SCHEMAVERSION TO TABLE_REDISTRIBUTION;
CREATE ROLE USER;
GRANT ROLE ADMIN TO USER;
GRANT SAVEPOINT ADMIN TO USER;
GRANT SCENARIO ADMIN TO USER;
GRANT EXECUTE ON SYS.TREXVIADBSL TO USER;
GRANT EXECUTE ON SYS.TREXVIADBSLWITHPARAMETER TO USER;
GRANT EXECUTE ON SYS.TRUNCATE_PROCEDURE_OBJECTS TO USER;
GRANT EXECUTE ON SYS.GET_PROCEDURE_OBJECTS TO USER;
GRANT EXECUTE ON SYS.GET_OBJECT_VERSION TO USER;
CREATE ROLE DBA_COCKPIT;
GRANT MONITORING TO DBA_COCKPIT;
GRANT BACKUP ADMIN TO DBA_COCKPIT;
GRANT CATALOG READ TO DBA_COCKPIT;
GRANT INIFILE ADMIN TO DBA_COCKPIT;
GRANT SERVICE ADMIN TO DBA_COCKPIT;
GRANT TRACE ADMIN TO DBA_COCKPIT;
GRANT TENANT ADMIN TO DBA_COCKPIT;
GRANT RESOURCE ADMIN TO DBA_COCKPIT;
GRANT SELECT ON SCHEMA _SYS_STATISTICS TO DBA_COCKPIT;
GRANT DELETE ON SCHEMA _SYS_STATISTICS TO DBA_COCKPIT;
GRANT UPDATE ON SCHEMA _SYS_STATISTICS TO DBA_COCKPIT;
GRANT INSERT ON SCHEMA _SYS_STATISTICS TO DBA_COCKPIT;
GRANT UPDATE ON _SYS_STATISTICS.STATISTICS_SCHEDULE TO DBA_COCKPIT;
GRANT EXECUTE ON SYS.MANAGEMENT_CONSOLE_PROC TO DBA_COCKPIT;
GRANT EXECUTE ON SYS.FULL_SYSTEM_INFO_DUMP_DELETE TO DBA_COCKPIT;
GRANT EXECUTE ON SYS.FULL_SYSTEM_INFO_DUMP_RETRIEVE TO DBA_COCKPIT;
GRANT EXECUTE ON SYS.FULL_SYSTEM_INFO_DUMP_CREATE TO DBA_COCKPIT;
GRANT SELECT ON SAP<SID>.SVERS TO DBA_COCKPIT;
GRANT SELECT ON SAP<SID>.CVERS TO DBA_COCKPIT;
GRANT SELECT ON SAP<SID>.LCAALERTS TO DBA_COCKPIT;
Now the final part, go to HANA Studio and assign these roles to DBACOCKPIT and SAPSID as per previous select statements.
Nice Article Marius Batrinu .
curious – Why  do we need DBACOCKPIT user ? and if we need to reset the password on hana level  where else have to update the password ?