Netezza – Not able to drop user
You may get following error when you try to drop any Netezza user:-
ERROR: DROP USER: user "username" owns objects, cannot be removed
As error explains itself, the user you are trying to drop is a owner of some objects. So you have to first transfer the ownership of those objects to ADMIN or some other user, before you can drop this user.
How to find all the objects owned by this user?
To find all the objects owned by user you can run below query in system database
select o.objname, d.database, oc.classname, u.username from _t_object o, _t_user u, _t_object_classes oc, _v_database d where u.username='USERNAME' and u.usesysid=o.objowner and o.objclass=oc.objclass and o.objdb=d.objid;
Output of this script will list out all the objects owned by this user in each database and the object type.
NOTE:- You may find some object types as CONSTRAINTS, for which you will not be able to change ownership with below script. Ideally constraints should be owned by same user who owns the base table, but it seems this is a possible defect in Netezza 6.05 P5 (in some special scenarios only) and the only way to change ownership of constraint (manually) is to drop and recreate the constraint.
Else to change the ownership of the objects to ADMIN, you can either create a custom script or make use of Netezza provided script nz_change_owner stored at /nz/support/bin . Syntax to run this script will be:-
bash$ pwd /nz/local/scripts bash$ nz_change_owner -from USERNAME -to ADMIN