Steps to hide SQL Database from Users

The SQL Server Alliance is becoming more popular in these days as a result various departments or client of the company would access same SQL Server Instance. When they worked in a combined instance, they do not want to make their database visible to other users. In this blog post we will discuss how to hide the database from users using Transact-SQL or using SQL Server Management Studio.

Using T-SQL

For this first we have to create two databases by using the following T-SQL query

Create database [Example_1]
go
Create database [Example_2]
Go

Create logins for both newly created databases

create login [Example_1Login] with
password='pa$$w0rd@123'
go
create login [Example_2Login] with
password='pa$$w0rd@123'
go

Use the following T-SQL script to hide all users database from all logins. The database then only visible to sysadmin and database owners

revoke view any database to [Example_1Login]
go
Revoke view any database to [Example_2Login]
go

As we know that only sysadmin and owners of the database can see database. So run the below statement to select Example_1Login as an owner of Example_1 and Example_2Login as an owner of Example_2

use [Example_1]
go
sp_changedbowner 'Example_1Login'
go
use [Example_2]
go
sp_changedbowner 'Example_2Login'
go

Using SQL Server Management Studio

Right click on the root section (Server Name) of SQL Server Management Studio >>Click on Properties

Under select page column, select Permission >> Click on the account, and select deny to view the database

In the next step, right click on the newly created database >>Click on Properties

Select Files >>Change the owner to newly created account

Now Example_1Login can see only Example_1 database and Example_2Login can see only Example_2 database

Conclusion

There are limited options to hide your database, as we discussed above. Once your database login gets restricted with DENY VIEW to PUBLIC then only the owner of the database and sysadmin can see the database