Can we delete session without SQL "ALTER SYSTEM CANCEL SESSION 'XXXXXX'?

[SAPSupport]

Can we delete session without SQL "ALTER SYSTEM CANCEL SESSION 'XXXXXX'"?

We can not execute kill session SQL because we use HEC service and we don't have the priviledge of SESSION ADMIN.
HEC answer SESSION ADMIN is not standard priviledge for customer.

When we meet error of lock entry, we need to request to hec team unlock the lock entry each time.
And this raising request and communication with HEC make the late to resolve the issue.
We would like to unlock the lock entry by ousrselves because a few hundred of the number of application member is stopped by this issue.

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

[SAPSupport · Accepted solution]

In SAP HANA Studio (SAP Note 2073112) you can terminate the current database request and transaction of a session via "Administration" -> "Performance" -> "Sessions" -> right-click on session -> "Cancel session".

In DBACOCKPIT (SAP Note 2222220) you can cancel the connections of active threads via "Performance" -> "Threads" -> "Cancel Threads".

With hdbcons (SAP Note 2222218) you can use the following command to cancel a connection with connection ID <conn_id>:

hdbcons 'connection c <conn_id>'

Refer More details in the KBA 2092196 - How-To: Terminating Sessions in SAP HANA

[lbreddemann]

This is an interesting Q&A from SAP Support.

Reading this I actually don't believe that the solutions mentioned enable someone to cancel a session, if the privilege for "ALTER SYSTEM CANCEL SESSION" is missing.

Furthermore, I am not sure if HEC customers routinely get access to the hdbcons tool.

As an alternative approach to situations like this, where certain system level privileges are required for specific actions, I created sudo_grant a few years back.

SUDO_GRANT combines a end user facing procedure with a dedicated audit setup that allows to execute a specific DBA action (granting a privilege in this case) for DB users that do not have that privilege themselves.

This procedure can easily be adapted e.g. for killing sessions.