PROBLEM:
Dropping a database user renders error:
ERROR: DROP USER: user "JOHN.DOUGH" owns objects, cannot be removed
What objects does user own?
SOLUTION:
SELECT distinct OWNER, THE_OBJECT_TYPE, OBJNAME, DBNAME FROM ( SELECT usename AS OWNER, (CASE WHEN objclass = 4902 then 'database' WHEN objclass = 4903 then 'group' WHEN objclass = 4904 then 'user' END ) AS the_object_type, objname, null as dbname FROM _t_object, _t_user WHERE objowner = usesysid AND objclass IN (4902, 4903, 4904) AND objdb = 0 AND objowner <> (SELECT usesysid FROM _t_user WHERE UPPER(usename) = 'ADMIN') UNION ALL SELECT _t_user.usename AS OWNER, lower(the_class.objname) AS the_object_type, the_object.objname as objname, the_database.objname AS dbname FROM _t_object as the_object LEFT OUTER JOIN _t_object the_class ON (the_object.objclass = the_class.objid), _t_object the_database, _t_user WHERE the_object.objowner = _t_user.usesysid AND the_object.objclass NOT IN (4902, 4903, 4904) AND the_object.objdb = the_database.objid AND the_object.objowner <> (SELECT usesysid FROM _t_user WHERE UPPER(usename) = 'ADMIN') ) tab WHERE 1=1 and OWNER in ( 'JOHN.DOUGH' ,'SUSAN.SMITH' ,'JUAN.RODRIGES' ) ORDER BY 1,2,3 ;