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, MS SQL users created through Plesk interface are not provided with db_owner role for security reasons. This is to harden SQL service security againts various attack, for example, SQL injection.

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.