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:
- Go to the folder page and select Managed Service for SQL Server.
- Click on the name of the cluster you need and select the tab Users.
- Click and select Configure.
- Add the desired databases for the user:
- Click Add database.
- Select the database from the drop-down list.
- Repeat the previous two steps until all the required databases are selected.
- To revoke access to a specific database, remove it from the list by clicking to the right of the database name.
- Set up user roles for each of the selected user's databases.
- Click in the Roles column.
- Select the role you want to add to the user from the drop-down list.
- Repeat the previous two steps until all the required roles are added.
- To revoke a role, click to the right of its name.
- Click Save.
update API method and pass the following in the request:
- In the
clusterIdparameter, 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
userNameparameter. 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.databaseNameparameter. To find out the name, get a list of databases in the cluster.
- Array of the new list of user roles, in the
- List of user configuration fields to be changed (in this case,
permissions), in the
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
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.