MSSQL user cannot see databases in SQL Server Management Studio on Plesk for Windows

Symptoms

Before Plesk Obsidian 18.0.82: MS SQL database users are able to see the names of MS SQL databases which are not owned by them.

Plesk Obsidian 18.0.82 and later: MS SQL database users cannot see their databases in the SQL Server Management Studio (SSMS) Object Explorer when connected with the database user credentials. The databases are listed under the sa login, and the databases work normally from websites and applications.

Cause

By default, MS SQL grants the VIEW ANY DATABASE permission to the public role. This permission does not give access to any database objects, but allows users to list the names of all databases on the SQL Server instance. MS SQL does not provide a way to configure the visibility of databases per user, except by denying this permission.

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): Plesk now explicitly denies VIEW ANY DATABASE to all new MS SQL users created by Plesk and, during the update, to all existing ones.

As a result, users can no longer list databases that belong to other customers. However, SSMS has a limitation: it does not show databases in Object Explorer if the VIEW ANY DATABASE permission is not granted, even for databases the user has access to. Other MS SQL clients (for example, the MSSQL extension for Visual Studio Code and HeidiSQL) are not affected.

Note: This is expected behavior after the fix and is not a malfunction of Plesk or the databases.

Resolution

Choose one of the following options:

Option 1. Use a client that lists the user's databases correctly, for example HeidiSQL or the MSSQL extension for Visual Studio Code.

Option 2. Query the databases the user has access to. For example, connect with SSMS and run a new query:

CONFIG_TEXT: SELECT database_id, name FROM sys.databases;

Option 3. Grant the VIEW ANY DATABASE permission to a specific user so that SSMS lists the databases:

Warning: After this change the user will be able to see the names of all databases on the server again. It is not recommended on shared servers. Plesk may deny this permission again on a future update.

1. Connect to the server via RDP.

2. Open SQL Server Management Studio either locally, or via remote connection.

3. Select the master database from the list of databases. Once the database is selected, click the button New Query.

4. Run the following SQL query, replacing username with the database user name:

CONFIG_TEXT: USE master;
GRANT VIEW ANY DATABASE TO [username];
GO

To deny the permission again, run:

CONFIG_TEXT: USE master;
DENY VIEW ANY DATABASE TO [username];
GO

Additional information

• Do not assign the db_owner role to MS SQL users created by Plesk to make databases visible. This is not necessary, because users can see the databases they have access to with SELECT database_id, name FROM sys.databases;.
• The master and tempdb databases are special in MS SQL and cannot be hidden.
• Servers on Plesk versions earlier than 18.0.82 can hide the database names from all users by revoking the permission from the public role (USE master; REVOKE VIEW ANY DATABASE TO PUBLIC; GO). SSMS will then not list the databases either.
• For more details on managing databases with a local client, see How to manage a database hosted in Plesk using a local database client?