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 as well as the objects (tables, views, macros, indexes, stored procedures, and triggers) owned by the transferred object.
Rules affecting transfer of ownership include:
- 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 it in the hierarchy). The new owner (and those above him in the hierarchy) gains implicit privileges on the object and all objects below it in the hierarchy.
Transferring ownership affects space allocation. Plan carefully and check space distribution and object ownership before using the GIVE statement.