Get started
Yandex Managed Service for SQL Server™

Granting user permissions and roles

The user created with a Managed Service for SQL Server cluster is automatically assigned the owner (DB_OWNER) role for the first database in the cluster. After that, you can create other users and configure their permissions as you wish.

Atomic permissions in SQL Server are called privileges and groups of permissions are called roles. Managed Service for SQL Server supports predefined roles. For more information about how to manage permissions, see the SQL Server documentation.

To grant a privilege to a particular user, create a user in the Yandex.Cloud management console or API, then run the GRANT command on behalf of the DB owner. For example:

GRANT SELECT ON Person.Address TO user1;
GO

To assign a role to a user, use the Yandex.Cloud management console or API: the roles assigned by the GRANT request are canceled during the next database operation.

Updating the list of user roles

To change a user's list of roles:

  1. Go to the folder page and select Managed Service for SQL Server.
  2. Click on the name of the cluster you need and select the tab Users.
  3. Click and select Configure.
  4. Add the desired databases for the user:
    1. Click Add database.
    2. Select the database from the drop-down list.
    3. Repeat the previous two steps until all the required databases are selected.
    4. To revoke access to a specific database, remove it from the list by clicking to the right of the database name.
  5. Set up user roles for each of the selected user's databases.
    1. Click in the Roles column.
    2. Select the role you want to add to the user from the drop-down list.
    3. Repeat the previous two steps until all the required roles are added.
  6. To revoke a role, click to the right of its name.
  7. Click Save.

Use the update API method and pass the following in the request:

  • In the clusterId parameter, the ID of the cluster where the user is located. To find out the cluster ID, get a list of clusters in the folder.
  • Username, in the userName parameter. To find out the name, get a list of users in the cluster.
  • The name of the database for which you want to change the list of user roles, in the permissions.databaseName parameter. To find out the name, get a list of databases in the cluster.
  • Array of the new list of user roles, in the permissions.roles parameter.
  • List of user configuration fields to be changed (in this case, permissions), in the updateMask parameter.

Warning

This API method resets any settings that aren't passed explicitly in the request to their defaults. To avoid this, be sure to pass the name of the field to be changed (in this case, permissions), in the updateMask parameter.

Description of predefined roles

  • DB_OWNER: Owner of the database. Users who have this role can perform any actions to configure and maintain the database, as well as delete the database in SQL Server.
  • DB_SECURITYADMIN: Security administrator. Users who have this role can manage privileges. They can extend their own access rights, so you need to track their actions.
  • DB_ACCESSADMIN: Access administrator. Users who have this role can grant or revoke remote database access rights for Windows users and groups and SQL Server users.
  • DB_BACKUPOPERATOR: Backup operator. Users who have this role can create database backups.
  • DB_DDLADMIN: DDL administrator. Users who have this role can execute any data description language (DDL) command.
  • DB_DATAWRITER: User with write permission. Users with this role can add, delete, or change data in any user table.
  • DB_DATAREADER: User with read permission. Users who have this role can read all the data from all user tables.
  • DB_DENYDATAWRITER: User who was denied write permission. Users who have this role can't add, modify, or delete the data in user tables in the database.
  • DB_DENYDATAREADER: User who was denied read permission. Users who have this role can't read the data from user tables in the database.
In this article: