How to add db_owner role to newly created users

Dmitry Bystrov

10 months ago

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:

Connect to the server via RDP;
Open the file panel.ini located in the folder %plesk_dir%adminconf (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%adminconfpanel.ini:

[databaseManagement]
features.roles.mssql.readWrite = db_owner,db_datareader,db_datawriter,db_backupoperator,db_ddladmin
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.

0 Shares