Creating and managing SQL Server users

This page describes how to do the following for Cloud SQL instances:

  • Configure the default user account.
  • Create, delete, and update other user accounts.

For information about how users work with Cloud SQL, see SQL Server Users.

Before you begin

Before completing the tasks below, create a Cloud SQL instance. For more information, see Creating instances.

If you plan to use your database's administrative client to manage users, you must have configured the default user on the instance. For more information, see Configuring the default account.

Configuring the default user account

When you create a new Cloud SQL instance, you must configure the default user account before you can connect to the instance.

For Cloud SQL for SQL Server, the default user is sqlserver.

To configure the default user:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Click the instance to open its Overview page.
  3. Select Users from the Navigation menu.
  4. Find the sqlserver user and select Change password from the more actions menu More actions icon..
  5. Provide a strong password that you can remember and click Ok.

gcloud

Set the password for the default user:

gcloud sql users set-password sqlserver \
    --instance=[INSTANCE_NAME] --prompt-for-password

REST v1beta4

The following request uses the users:update method to update the root user account

Before using any of the request data below, make the following replacements:

  • project-id: Your project ID
  • instance-id: The desired instance ID
  • user-id: The ID of the user
  • password: The password for the user

HTTP method and URL:

PUT https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users?name=user-id

Request JSON body:

{
  "name": "user-id",
  "password": "password"
  }

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-02-07T22:38:41.217Z",
  "startTime": "2020-02-07T22:38:41.217Z",
  "endTime": "2020-02-07T22:38:44.801Z",
  "operationType": "UPDATE_USER",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Creating a user

To create a user:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Users from the Navigation menu.
  4. Click ADD USER.
  5. In the Add a user account to instance instance_name page, add the following information:
    • A Username.
    • A Password.
    • Optionally, a Host.

      The default is '%', which means that the host list is unrestricted. You can also provide an IP address or IP address range, which means that only users connecting from those addresses will be able to access the database.

  6. Click Create.

gcloud

Create the user:

gcloud sql users create [USER_NAME] \
   --instance=[INSTANCE_NAME] --password=[PASSWORD]

User name length limits are the same for Cloud SQL as for on-premises SQL Server.

REST v1beta4

The request below uses the users:insert method to create a user account 'user_name'.

Before using any of the request data below, make the following replacements:

  • project-id: Your project ID
  • instance-id: The desired instance ID
  • user-id: The ID of the user
  • password: The password for the user

HTTP method and URL:

POST https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users

Request JSON body:

{
  "name": "user-id",
  "password": "password"
  }

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-02-07T22:44:16.656Z",
  "startTime": "2020-02-07T22:44:16.686Z",
  "endTime": "2020-02-07T22:44:20.437Z",
  "operationType": "CREATE_USER",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

User name length limits are the same for Cloud SQL as for on-premises SQL Server.

Changing a user password

To change a user password:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Users from the Navigation menu.
  4. Click more actions More actions icon. for the user you want to update.
  5. Select Change password, specify a new password, and click OK.

gcloud

Update the password:

gcloud sql users set-password [USER_NAME] \
   --instance=[INSTANCE_NAME] --prompt-for-password

REST v1beta4

The following request uses the users:update method to update the password for the user account 'user_name'.

Before using any of the request data below, make the following replacements:

  • project-id: Your project ID
  • instance-id: The desired instance ID
  • user-id: The ID of the user
  • password: The password for the user

HTTP method and URL:

PUT https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users?name=user-id

Request JSON body:

{
  "name": "user-id",
  "password": "password"
  }

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-02-07T22:38:41.217Z",
  "startTime": "2020-02-07T22:38:41.217Z",
  "endTime": "2020-02-07T22:38:44.801Z",
  "operationType": "UPDATE_USER",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

Updating users

For information related to database-level roles in SQL Server, see Working with Database-Level Roles in the SQL Server documentation.

Listing users

To list users:

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Users from the Navigation menu.

gcloud

List the users for this instance:

gcloud sql users list --instance=[INSTANCE_NAME]

For a complete list of parameters for this command, see the gcloud sql users list reference page.

REST v1beta4

The request below uses users:list method to list the users defined for an instance.

Before using any of the request data below, make the following replacements:

  • project-id: Your project ID
  • instance-id: The desired instance ID

HTTP method and URL:

GET https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#usersList",
  "items": [
    {
      "kind": "sql#user",
      "etag": "--redacted--",
      "name": "sqlserver",
      "host": "",
      "instance": "instance-id",
      "project": "project-id",
      "sqlserverUserDetails": {
        "serverRoles": [
          "CustomerDbRootRole"
        ]
      }
    },
    {
      "kind": "sql#user",
      "etag": "--redacted--",
      "name": "user-id-1",
      "host": "",
      "instance": "instance-id",
      "project": "project-id",
      "sqlserverUserDetails": {
        "serverRoles": [
          "CustomerDbRootRole"
        ]
      }
    },
    {
      "kind": "sql#user",
      "etag": "--redacted--",
      "name": "user-id-2",
      "host": "",
      "instance": "instance-id",
      "project": "project-id",
      "sqlserverUserDetails": {
        "serverRoles": [
          "CustomerDbRootRole"
        ]
      }
    },
    {
      ...
    },
    {
      ...
    }
  ]
}

Deleting users

To delete a user:

Before deleting a user, you must drop all the objects it owns or reassign their ownership, and revoke any privileges the role has been granted on other objects.

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select the instance to open its Overview page.
  3. Select Users from the Navigation menu.
  4. Click more actions More actions icon. for the user you want to delete.
  5. Select Delete and click OK.

gcloud

Delete the user:

gcloud sql users delete [USER_NAME] --host=[HOST] --instance=[INSTANCE_NAME]

REST v1beta4

The request below uses the users:delete method to delete the specified user account.

Before using any of the request data below, make the following replacements:

  • project-id: Your project ID
  • instance-id: The desired instance ID
  • user-id: The ID of the user

HTTP method and URL:

DELETE https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/users?host=&name=user-id

Request JSON body:

{
  "name": "user-id",
  "host": ""
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "DONE",
  "user": "user@example.com",
  "insertTime": "2020-02-07T22:38:41.217Z",
  "startTime": "2020-02-07T22:38:41.217Z",
  "endTime": "2020-02-07T22:38:44.801Z",
  "operationType": "DELETE_USER",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

What's next