sql server 中隐藏掉无关数据库

先贴上我实际测试的效果

方法一：

Problem

I have a SQL Server instance that has hundreds of databases.  Navigating the database tree in SSMS is a pain and I was wondering if there was a way to limit the list of databases that I see in SSMS?

Solution

SQL Server consolidation is becoming more popular these days to reduce costs and therefore more and more databases are being put on one instance. It is very common to host multiple databases on a consolidated instance from multiple applications and departments and sometimes application owners want to hide their databases to other users of the instance. They do not want to make their database visible to others. This tip will give you an understanding on how databases can be hidden.

Setup

Suppose there are two databases A and B from two different applications and they are hosted on the same SQL Server instance. The users of database A are not allowed to see database B and vice versa. Here we will create two different logins user_A and user_B and give them appropriate rights to their own databases.

CREATE DATABASE A
GO
CREATE DATABASE B
GO
CREATE LOGIN user_A with password='U$er_A@1234'
Go
CREATE LOGIN user_B with password='U$er_B@1234'
Go
USE A
GO
CREATE USER user_A for login user_A;
GO
EXEC sp_addrolemember 'db_owner', 'user_A'
GO
USE B
GO
CREATE USER user_B for login user_B
GO
EXEC sp_addrolemember 'db_owner', 'user_B'

NOTE:-DO NOT MAKE CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRNOMENTS

Hiding all user databases for all logins

Suppose you want to hide all databases for all