How to add db_owner role to newly created users

Question

How to add db_owner role to newly created MSSQL users?

Answer

Starting from Plesk version 12, MSSQL users created through Plesk interface are not provided with db_owner role for security reasons.

In order to change this behavior for server wide perform the following steps:

1. Connect to the server via RDP;

2.  Open the file panel.ini located in the folder %plesk_dir%admin\conf\ (Just paste this path in Windows Explorer to open) in any text editor.

   Note: If the panel.ini file does not exist, create it by copying the sample configuration file panel.ini.sample to panel.ini.

   Add the following settings to the %plesk_dir%admin\conf\panel.ini:

   CONFIG_TEXT: [databaseManagement]
   features.roles.mssql.readWrite = db_owner,db_datareader,db_datawriter,db_backupoperator,db_ddladmin

3. Run the following CMD 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.

Refer to the documentation Database User Roles and Permissions for more details.

For one particular database it's possible to assign db_owner role via Microsoft SQL Management Studio Express:

• Open Microsoft SQL Management Studio Express
• Navigate to Security > Logins > Right-click the db user and select Properties
• In properties go to User Mappings
• Click on the database and check that the options db_owner and Public are selected.