How to drop a user on Netezza

You probably know the sql statement to drop a user

DROP USER pippo;

but you could get this error

ERROR:  DROP USER: user "PIPPO" owns objects, cannot be removed

This article shows how to solve this problem.

The short answer is

just run this query

select database, objtype, objname
from _V_OBJ_RELATION_XDB
where owner = ^pippo^

you will know the objects owned by the user you want to drop. You should alter those objects and transfer their ownership to another user.

You can install my Netezza utilities to simplify this task and save time.

Get the list of objects owned by user pippo.

CALL util..objects_owned_by('pippo');

Then connect to every database containing objects owned by pippo and transfer ownership to user pluto.

\c mydatabase
CALL util..transfer_objects_owned_by('pippo', 'pluto');