How to drop a user on Netezza

You are trying to drop a user but Netezza complains cause it "owns objects"? This article shows how to solve this problem.

SQL

Problem

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

Solution

The short answer is: just run this query

SELECT database, objtype, objname
FROM _V_OBJ_RELATION_XDB
WHERE owner = ^pippo^

which shows you 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');

Finally, when no other object is owned by pippo you will be able to drop it.