How to provide CREATE ROLE permission for MSSQL database users

Question

How to provide ‘CREATE ROLE’ permission for MSSQL database users?

Answer

According to the documentation Database User Roles and Permissions , Plesk provides three types of roles for MSSQL database users in Plesk: ‘Read and Write’, ‘Read Only’, ‘Write Only’, and each one has its own number of MSSQL roles.

‘CREATE ROLE’ permission requires a membership in the db_securityadmin fixed database role.

In order to add ‘CREATE ROLE’ permission for database users perform the following steps:

1. Add the
db_securityadmin
fixed database role to the one of the role (e.g. readWrite) by adding the following in
%plesk_dir%adminconfpanel.ini
:

   [databaseManagement]
   features.roles.mssql.readWrite = db_securityadmin,db_datareader,db_datawriter,db_backupoperator,db_ddladmin

2. Run the following command to apply the changes to all the MSSQL servers:

   C:> “%plesk_cli%repair.exe” –update-mssql-users-permissions

Note: the existing users will obtain a new role as well.

Additionally: refer to the Microsoft CREATE ROLE documentation for more details.