After upgrade to Plesk Obsidian 18.0.82 version, the MS SQL database users cannot see their databases in the SQL Server Management Studio (SSMS)

Symptoms

After upgrade to Plesk Obsidian 18.0.82 version, the MS SQL database users cannot see their databases in the SQL Server Management Studio (SSMS)

Cause

Previously, Plesk for Windows did not deny the VIEW ANY DATABASE permission to the MS SQL users it creates, so any user could list the names (and only the names) of all databases on the server.

This was fixed in Plesk Obsidian 18.0.82 (PPP-68783) and Plesk explicitly denied VIEW ANY DATABASE to all new MS SQL users created by Plesk and, during the update, to all existing ones.

At the same time, due to a limitation in SQL Server Management Studio (SSMS), MS SQL users without the VIEW ANY DATABASE permission could no longer list databases in SSMS, including the database assigned to them.

This behavior was also considered as a Plesk bug with the ID #PPPM-15603, which was fixed in 18.0.82.3 version

Resolution

In order to allow MS SQL users to see databases in SSMS

1. Upgrade Plesk to the latest 18.0.82.3 version
2. Log into the server via RDP.
3. Start a command prompt as an Administrator

4. Execute the command below to open the %plesk_dir%admin\conf\panel.ini file 

   C:\> plesk conf panel.ini

5.  Add the following lines at the bottom of the file:

   CONFIG_TEXT: [databaseManagement]
   ; Plesk revokes explicit (GRANT/DENY) VIEW ANY DATABASE permission.
   mssqlViewAnyDatabase = revoke

6.  Save the changes and close the file

7. Run the next command to apply the changes:

   C:\> plesk bin repair --update-mssql-users-permissions

8. Again execute the command below to open panel.ini file

   C:\> plesk conf panel.ini

9. Remove the previously added lines:

   CONFIG_TEXT: [databaseManagement]
   ; Plesk revokes explicit (GRANT/DENY) VIEW ANY DATABASE permission.
   mssqlViewAnyDatabase = revoke

10. Save the changes and close the file