> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# REASSIGN OWNED

The `REASSIGN OWNED` statement changes the [ownership](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#object-ownership) of all database objects (i.e., tables, types, or schemas) in the current database that are currently owned by a specific [role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#roles) or [user](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#sql-users).

> [!NOTE]
>
> The `REASSIGN OWNED` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](https://docs.cockroachlabs.com/docs/stable/online-schema-changes.md).

> [!TIP]
>
> To change the ownership of any single object (e.g., a table or a database), use the `OWNER TO` subcommand of the object's [`ALTER` statement](https://docs.cockroachlabs.com/docs/stable/sql-statements).

## Required privileges

* To reassign ownership with `REASSIGN OWNED`, the user must be a member of the current owner's role and a member of the target owner's role.
* Members of the [`admin` role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization#admin-role) can always use `REASSIGN OWNED BY`.

## Syntax

![reassign\_owned\_by syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/reassign_owned_by.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=cb73fd9a00b7fe0b11f2c9a3087b1ca5)

## Parameters

| Parameter | Description |
| - | - |
| `role_spec_list` | The source role, or a comma-separated list of source roles. |
| `role_spec` | The target role. |

## Example

#### Setup

To follow along, run [`cockroach demo`](https://docs.cockroachlabs.com/docs/stable/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](https://docs.cockroachlabs.com/docs/stable/movr) sample dataset preloaded:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```

### Change the owner of all tables in a database

Suppose that the current owner of the `users`, `vehicles`, and `rides` tables in the `movr` database is a role named `cockroachlabs`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE ROLE cockroachlabs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT CREATE ON DATABASE movr TO cockroachlabs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE users OWNER TO cockroachlabs;
> ALTER TABLE vehicles OWNER TO cockroachlabs;
> ALTER TABLE rides OWNER TO cockroachlabs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES;
```

```
  schema_name |         table_name         | type  |     owner     | estimated_row_count | locality
--------------+----------------------------+-------+---------------+---------------------+-----------
  public      | promo_codes                | table | demo          |                1000 | NULL
  public      | rides                      | table | cockroachlabs |                 500 | NULL
  public      | user_promo_codes           | table | demo          |                   0 | NULL
  public      | users                      | table | cockroachlabs |                  50 | NULL
  public      | vehicle_location_histories | table | demo          |                1000 | NULL
  public      | vehicles                   | table | cockroachlabs |                  15 | NULL
(6 rows)
```

Now suppose you want to change the owner for all of the tables owned by `cockroachlabs` to a new role named `movrlabs`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE ROLE movrlabs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT CREATE ON DATABASE movr TO movrlabs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> REASSIGN OWNED BY cockroachlabs TO movrlabs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES;
```

```
  schema_name |         table_name         | type  |  owner   | estimated_row_count | locality
--------------+----------------------------+-------+----------+---------------------+-----------
  public      | promo_codes                | table | demo     |                1000 | NULL
  public      | rides                      | table | movrlabs |                 500 | NULL
  public      | user_promo_codes           | table | demo     |                   0 | NULL
  public      | users                      | table | movrlabs |                  50 | NULL
  public      | vehicle_location_histories | table | demo     |                1000 | NULL
  public      | vehicles                   | table | movrlabs |                  15 | NULL
(6 rows)
```

## See also

* [Authorization](https://docs.cockroachlabs.com/docs/stable/authorization.md)
* [`ALTER TABLE ... OWNER TO`](https://docs.cockroachlabs.com/docs/stable/alter-table#owner-to)
* [`SHOW TABLES`](https://docs.cockroachlabs.com/docs/stable/show-tables)
* [SQL Statements](https://docs.cockroachlabs.com/docs/stable/sql-statements.md)