SQL statement to hide all system databases in Server from a server login [duplicate]

Question

I want to create a Windows authentication server login and make it cannot see any system databases e.g.master, model.

I know that we can control view definition in database level e.g. database user, database role but I don't want to configure them to all system databases.

Therefore, I started by:

1. Create Windows authentication login
2. Create custom server role
3. Deny view definition to a custom server role
4. Add an login to a role

I hope the login shouldn't see all system databases but it still can see master and tempdb under System Databases node.

This is the screenshot of the result.

and these are my SQL statements.

-- Create login from Windows User
USE MASTER
GO

CREATE LOGIN [domain\web_user] FROM WINDOWS WITH 
DEFAULT_DATABASE = MyTestDB
GO

USE MyTestDB
GO

CREATE USER web_user FOR LOGIN [domain\web_user]
WITH DEFAULT_SCHEMA = dbo 
GO

EXEC sp_addrolemember @rolename = 'DB_DataReader', 
@membername = 'web_user'
GO



--Create custom server role, deny view definition and add login to role
USE master
GO

CREATE SERVER ROLE junior
GO 

DENY VIEW ANY DEFINITION TO [junior]
GO

ALTER SERVER ROLE junior ADD MEMBER [domain\web_user] ​
GO

Answer 1

From this post hide system databases sql server management studio.

I think it solves my problem. However, for normal login and user, we can't hide master and tempdb database.

The following information are in this document. hide system databases sql server management studio

After this permission is denied, a login can see only metadata for master, tempdb, and databases that the login owns.