How to Prevent users from viewing all Microsoft SQL databases March 22, 2025

This article illustrates how to prevent users from viewing all Microsoft SQL databases as the users with basic public access can often view a list of all databases on the server. To improve security and restrict unauthorised access, administrators can implement specific permissions to prevent users from seeing databases they do not have access.

Method 1: Using the DENY VIEW ANY DATABASE Permission

1. Connect to SQL Server
   Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
2. Select the User or Role
   1. Identify the user or role you want to restrict.
   2. You can use the following query to list users:

      SELECT name FROM sys.database_principals WHERE type IN ('S', 'U');

3. Deny the Permission
   1. Run the following SQL command to prevent the user from viewing any databases:

      DENY VIEW ANY DATABASE TO PUBLIC;
      DENY VIEW ANY DATABASE TO [username];

   2. Replace [username] with the actual user or role.
4. Verify the Changes
   Log in with the restricted user account to ensure that databases are no longer visible.

Method 2: Assign Users to Specific Databases
Instead of granting server-wide access, assign users only to the databases they need to access.

1. Create a New Login (if the user does not exist)

   CREATE LOGIN restricted_user WITH PASSWORD = 'StrongPassword';

2. Create a Database User for the Specific Database

   USE TargetDatabase;
   CREATE USER restricted_user FOR LOGIN restricted_user;

3. Grant Limited Permissions

   GRANT CONNECT TO restricted_user;

4. Test the User’s Access
   Log in as the restricted user and confirm that only the assigned database is visible.

Method3:Remove Public Role Access
By default, the public role grants some visibility to databases. You can revoke unnecessary permissions:

REVOKE CONNECT FROM public;
DENY VIEW ANY DATABASE TO public;

Use this method carefully, as it may impact other users who rely on minimal access.

This way, we can conclude that restricting database visibility is essential in securing your SQL Server environment.

If you have any questions, feel free to contact our support team.