Transfer databases and users from one immediate owner to another using the GIVE statement, for example:
GIVE Finance TO Alice;
The GIVE statement also transfers all child databases and users and the objects (tables, views, macros, triggers, indexes, and stored procedures) owned by the transferred object.
The following rules affect transfer of ownership:
- You cannot give an object to children of the object.
- The permanent space owned by the database or user is also transferred.
- To use GIVE, you must have the explicit DROP DATABASE/USER privilege on the object being transferred and explicit DROP/CREATE DATABASE/USER privilege on the receiving object.
- Any explicit privileges granted to others on a transferred user are not automatically revoked. You need to explicitly REVOKE explicit privileges.
- Changing ownership hierarchy impacts implicit privileges. When a database or user has a new owner, the former owner loses implicit privileges on the object (and all objects below the database or user in the hierarchy). The new owner (and those above the new owner in the hierarchy) gains implicit privileges on the object and all objects below the new owner in the hierarchy.
Transferring ownership affects space allocation. Plan carefully and check space distribution and object ownership before using the GIVE statement.