How to make in mssql so that the user could not see certain databases at all?

Question

MySQL

In mssql, it turns out to configure access so that the user cannot read databases to which he does not have access. But the problem is that the user can see these bases. How in mssql to make it so that the user could not see databases to which he does not have access at all?

Answer 1

It is very important to understand the difference between user and login in SQL Server. A two-level authentication system is used here: logins (Security - Logins) are used to enter the instance (a running database instance, there may be several on one machine) of the server. For logins, there are roles and rights associated with administration and obtaining information about the instance. Users (Databases - Security - Users) exist at the database level, they also have their own roles and rights associated with the interaction with the database. What you want to do is prevent a login from seeing all databases unless he is a sysadmin and owns some database. The best way to do this is to create a server role and include the login in it:

create server role blind_users;

deny view any database to blind_users;

alter server role blind_users add member login_name;