objects owned by a user in HANA

[Former Member]

Hi Folks,

I have a question is there a way to get to know what all objects are owned by a particular user.

I was trying to drop one of the users and i am getting a message to use the cascade option. But before doing it i want to know what all objects are owned by the user so that i can take a call to drop the user.

Is there any view which gives this information.

DROP USER - SAP HANA SQL and System Views Reference - SAP Library

[Former Member · Accepted solution]

Hello,

I'll just expand upon what Florian has already provided with a working example.

Lars/Richard,

If I'm in breach of anything (legalese), please dump my reply to the "sin bin".

Source: Excellent book by and (aka the Blues Brothers)

SAP HANA Administration. of Richard Bremer, Lars Breddemann - by SAP PRESS

(select SCHEMA_NAME, '' as OBJECT_NAME, 'SCHEMA' as OBJECT_TYPE, SCHEMA_OWNER as OBJECT_OWNER
from "PUBLIC"."SCHEMAS" where SCHEMA_OWNER = 'XXXX'
UNION ALL
select SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE, OWNER_NAME
from "PUBLIC"."OWNERSHIP"
where SCHEMA_NAME in (
select SCHEMA_NAME from "PUBLIC"."SCHEMAS" where SCHEMA_OWNER = 'XXXX' or OWNER_NAME = 'XXXX' )
) order by SCHEMA_NAME asc, OBJECT_NAME asc

Of course replace with 'XXXX' with the values pertinent to you.

Cheers,

Amerjit

[lbreddemann · Accepted solution]

Thanks for that Amerjit!

Good job answering the question.

Cheers, Lars

[Former Member · Accepted solution]

Thanks a ton amerjit, will try out and let you know.

[Former Member · Accepted solution]

Thanks a ton Lars. What a gift it is to you have to the HANA administrators.

[pfefferf]

Hello,

you find this information in system view OWNERSHIP: OWNERSHIP - SAP HANA SQL and System Views Reference - SAP Library

It displays all objects created by the user in schemas different to the users own schema.

Best Regards,

Florian

[Former Member]

Thanks a ton Florian, will try out and let you know.