Get started
Yandex Managed Service for SQL Server™

Assigning privileges and roles to users

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.

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:

Updating the list of user roles

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.

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.

Granting a privilege to a user

  1. Connect to the database under the account of the database owner.
  2. Run the GRANT command. To learn more about the command syntax, see the documentation for SQL Server.

Revoking a privilege from a user

  1. Connect to the database under the account of the database owner.
  2. Run the REVOKE command. To learn more about the command syntax, see the documentation for SQL Server.

Examples

Add a user with read-only permissions

To add a new user user2 to an existing cluster with read-only access to the db1 database:

  1. Create a user with the name user2. Select the databases that the user should have access to.

  2. Connect to the db1 database under the account of the database owner.

  3. To only grant access rights to the Products table, in the default dbo schema, run the command:

    GRANT SELECT ON dbo.Products TO user2;
GO

  4. To grant access permissions to all the myschema schema tables, run the command:

    GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::myschema TO user2;
GO

To revoke the granted privileges, run the commands:

REVOKE SELECT ON dbo.Products FROM user2;
REVOKE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::myschema FROM user2;
GO

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: