Search This Blog

SQL Hide database


This is a Level-100 topic because today one technical person ask me to hide the other databases for a user and he/she will see only his/her database where the permission granted. I thought to publish this too, as this may help to others as well. Below is step by step script:
1) USE MASTER;
2) CREATE DATABASE D_SAMPLE;
3) CREATE LOGIN U_SAMPLE WITH PASSWORD =’PA$$W0RD’;
4) CREATE USER U_SAMPLE FOR LOGIN U_SAMPLE;
5) DENY VIEW ANY DATABASE TO U_SAMPLE;
6) ALTER AUTHORIZATION ON DATABASE::D_SAMPLE TO U_SAMPLE;
Now connect using SSMS with created user and the user will see only the created database along with master and tempdb.