Dropping users in Amazon Redshift is a simple task, but sometimes you may encounter an error message if the user you are trying to drop is an owner of an object in the database, such as a schema, table, or view.

When this happens, you will see one of the following error messages:

ERROR: user "user1" can't be dropped because the user owns some object [SQL State=55006]
ERROR: user "user1" can't be dropped because the user has a privilege on some object [SQL State=55006]

These error messages are mentioned in the AWS documentation for the DROP USER command. In this blog post, I will show you how to handle these errors by checking a user’s permissions, changing the owner of the relevant objects, and then dropping the user.

Check database object owners

First, you need to determine which objects the user owns or has privileges on. You can do this by running the following query:

SELECT *
FROM SVV_USER_PERMISSIONS
WHERE grantee = 'user1';

This will return a list of all the objects that the user has permissions on. You can then use this information to identify the objects that you need to change the owner for.

Change object owner

To change the owner of an object, you can use the ALTER command. For example, to change the owner of a schema called my_schema to a user called user2, you can use the following command:

ALTER SCHEMA my_schema OWNER TO user2;

You will need to repeat this step for each object that the user owns or has privileges on.

Drop user

Once you have changed the owner of all the relevant objects, you can drop the user using the DROP USER command. For example:

DROP USER user1;

That’s it! By following these steps, you should be able to successfully drop a user without encountering any errors.

As always, it’s important to be careful when modifying your database. Make sure to create backups and test your changes before applying them to your production environment. Also, have a look at the DROP USER AWS Docs.