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

# REASSIGN OWNED

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `REASSIGN OWNED` statement changes the <InternalLink path="security-reference/authorization#object-ownership">ownership</InternalLink> of all database objects (i.e., tables, types, or schemas) in the current database that are currently owned by a specific <InternalLink path="security-reference/authorization#roles">role</InternalLink> or <InternalLink path="security-reference/authorization#sql-users">user</InternalLink>.

<Note>
  The \`\` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>.
</Note>

<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 <InternalLink path="sql-statements">`ALTER` statement</InternalLink>.
</Tip>

## 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 <InternalLink path="security-reference/authorization#admin-role">`admin` role</InternalLink> can always use `REASSIGN OWNED BY`.

## Syntax

<img src="https://mintcdn.com/cockroachlabs/M1Nto-joXUTgisRs/images/sql-diagrams/v25.3/reassign_owned_by.svg?fit=max&auto=format&n=M1Nto-joXUTgisRs&q=85&s=00a7e1a299b1c304528ed4017f8d1ac9" alt="reassign_owned_by syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="587" height="37" data-path="images/sql-diagrams/v25.3/reassign_owned_by.svg" />

## 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 <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> to start a temporary, in-memory cluster with the <InternalLink path="movr">`movr`</InternalLink> 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

* <InternalLink path="authorization">Authorization</InternalLink>
* <InternalLink path="alter-table#owner-to">`ALTER TABLE ... OWNER TO`</InternalLink>
* <InternalLink path="show-tables">`SHOW TABLES`</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>