Skip to Content
avatar image
Former Member

objects owned by a user in HANA

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jun 06, 2015 at 09:32 AM

    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 @Lars Breddemann and @Richard Bremer (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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 06, 2015 at 05:40 AM

    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

    Add comment
    10|10000 characters needed characters exceeded