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.
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
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
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
