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.
- Contained Databases
- Contained Databases – Pros and Cons
- Security Best Practices with Contained Databases
- SQL SERVER – Security Considerations for Contained Databases
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