CODEWITHSUNDEEP
amazon-redshift
MiguelPeralvo
MiguelPeralvo

Reputation: 877

Redshift - user "xyz" cannot be dropped because the user owns some object

When trying to drop a user "xyz" in a Redshift database, I get the error message:

user 'xyz' cannot be dropped because the user owns some object.

According to the documentation:

If a user owns an object, first drop the object or change its ownership to another user before dropping the original user

How do I know which objects (schemas, tables, views, UDFs?, ...) are owned by the user?

Upvotes: 7

Views: 6654

Answers (4)

alexanderlz
alexanderlz

Reputation: 589

You can see the tables and views owned by a specific user in pg_tables and pg_views system tables.

(There is also pg_udf, but it doesn't hold any reference to specific user.)

select 
    case when schemaname='public' then '' 
        else schemaname+'.' end+tablename 
    from pg_tables where tableowner = 'xyz'
union 
    select 
    case when schemaname='public' then ''
        else schemaname+'.' end+viewname 
    from pg_views where viewowner = 'xyz';

Upvotes: 4

Bohemian
Bohemian

Reputation: 425258

In my case, the user didn't own any tables or views or anything else that I could find. I guessed correctly that having been granted a privilege meant the user "owned" something, because I fixed the problem with this:

revoke all privileges on database some_database_name from some_user_name

after which the drop user command worked.

Upvotes: 5

femeloper
femeloper

Reputation: 96

The following blog post by AWS explains very clearly how to resolve this issue: https://aws.amazon.com/premiumsupport/knowledge-center/redshift-user-cannot-be-dropped/

Their script v_find_dropuser_objs.sql link here returns a nice table of object type, object owner, user id, schema name, object name, ddl etc.

Upvotes: 0

Ville
Ville

Reputation: 4346

Since the following resolved the problem in so spectacularly easy fashion for me, I duplicate this response into this thread as well:

After trying suggestions from countless posts and threads, awslabs' aws-redshift-utils provided relief in the form of admin.v_find_dropuser_objs view. It instantly identified the remaining dependencies making it possible to drop the user in question.

Upvotes: 6

Related Questions