How to hide SQL databases from users using SSMS

I know the First question you will ask it “Why we need to hidden it IF the user doesn’t have access on it and he can’t do any operation on this DB 🤔” Simple answer “Business needs & Business Case & Management Request” Whatever the answer but this is the request from management So let us see How we can do it using two solutions.

First Solution hide the DB using Permissions

Before starting on the solution, I need to highlight important point “to implement this solution the user will take ownership of the DB” and this will break the security role. To hide the DB, we need two steps after it the user when he will log in to SQL Server management studio, he will see only this database:

Deny or remove the permission of View on databases from the Public User on the instance level.

At this time no one will able to see any databases on the SQL Server management studio except the admins only this means you should grant this permission for specific users that you need them to view all databases “It is just view Permission, accessing the DB is another permission”.

Change the DB Authorization by the user name you need him to view this DB only or you can do this step on two or three or more databases at this time this user when he login to SQL Server Management studio he will view these databases only the other databases will be hidden.

Technical deployment scripts

Create two new Database “demohidedb1” “demohidedb1 and on SQL Server
Create new User “demotestuser”
Deny view any database from public user
Change DB “demohidedb” Authorization by new SQL user “demotestuser”
Connect to SQL Server management studio

CREATE DATABASE [demohidedb1]
GO
CREATE DATABASE [demohidedb2]
GO
USE [master]
GO
CREATE LOGIN [demotestuser] WITH PASSWORD=N'P@$$w0rd', 
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF
GO
DENY VIEW ANY DATABASE TO [PUBLIC]
GO
USE [demohidedb1]
GO
ALTER AUTHORIZATION ON DATABASE::[demohidedb1] TO [demotestuser]
GO

Now you will see the user connected to SQL Server but he able to see only the DB “demohidedb1” and not able to see the second DB we created it “demohidedb2” but take care the user has full access on the database this meaning he can create, delete and alter any object on DB level.

The second Solution using a Contained Database

Contained Database it is an isolated database from the other databases hosted on the same SQL Server, the and it has pros and cons and it is not supporting some features, so before going for this option I highly recommend you to read more about it and you can take a look on below articles it will help you.

For more information about Contained Databases from Security perspective i highly recommend to read this article (Partially Contained Databases in SQL Server and its Security Implication) for Mr. Emad and for more information about him check his page

Technical deployment scripts

Enabled contained database option on SQL Server instance and this configuration not required any restart
Create Contained DB
Check and select List by Contained DB
Create a user on Contained DB with granting Read-only access.
Test the Connection

--Enabled contained database on SQL Server 
USE MASTER
GO
EXEC sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'contained database authentication', 1
GO
RECONFIGURE
GO
---Creat Contained DB
CREATE DATABASE [DemoContainedDB]
CONTAINMENT = PARTIAL
--List by Contained DB 
select containment,name from sys.databases where containment = 1
--Create user on Contained DB 
USE [DemoContainedDB]
GO
CREATE USER [demotestuser2] WITH PASSWORD=N'P@$$w0rd', DEFAULT_SCHEMA=[dbo]
GO
USE [DemoContainedDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [demotestuser2]
GO

Now the user ready to login but the login concept for a contained database is different than the normal database.

Open SQL Server Management Studio
Select your server name
Then click on options
Write the contained DB name on the option of Connected to a database so it should be “DemoContainedDB” instead of “Default”
Mark the option of Trust Server Certificate
Return to login and write the user name and password

By these steps, you can connect to Contained Database and you will find the user “demotestuser2” can see only the contained DB “DemoContainedDB”

Test it and let me know is it worked with you or no , and if you have other solutions share it in the comment Thanks for your time and I hope this information is helpful to keep following my latest posts and articles check below social accounts