Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If you are using a consolidated SQL Server environment, there is more than one database and login on an instance. When the logins connect to the instance, they see other databases by default. But if you did not authorize a login on a database, it cannot display anything in the database.
It is a security weakness that the logins see the database names that are not authorized.
Therefore, you must prevent the login from seeing the database names that they are not authorized.
For example, let’s create a Login named DenyLogin and authorize db_datareader to this Login on the AdventureWorks2014 database. You can find the details of how to do this in the article “How To Create a Login On SQL Server(Manage Logins)”
Using SSMS, let’s connect to instance with DenyLogin. You will be able to see other databases that we have not been authorized. But when you click the + sign next to the databases, it will give an error as follows.
The database Test is not accessible.
Run the following command with another authorized user.
1 2 3 4 | USE master; GO DENY VIEW ANY DATABASE TO [DenyLogin]; GO |
Then, on the SSMS, go to the part you are connected to with DenyLogin. Right-click the Databases tab and refresh. This time you will notice that no database appears. Because we’ve given DenyLogin the authority to just read.
You need to give DenyLogin the owner authorization with the script below.
1 2 3 4 | USE master; GO ALTER AUTHORIZATION ON DATABASE::AdventureWorks2014 TO [DenyLogin]; GO |
When you try to run the script above, you will see an error like the one below.
Msg 15110, Level 16, State 1, Line 13
The proposed new database owner is already a user or aliased in the database.
The description of this error is: In the AdventureWorks2014 database, you want to set up DenyLogin as the owner. However, there is currently a User or alias named DenyLogin in the database. The reason is that we give this login the authority to read on the database. The reason is that we give read authority to this login with map processes on the database.
When the Map operation is performed, SQL Server has created a User named DenyLogin in the AdventureWorks2014 database. To overcome this problem, you can delete the DenyLogin user in the database with the help of the following script. After deleting, it will be completed successfully when you run the script starting with ALTER AUTHO .. above.
When you refresh the Databases tab, you will be able to see the AdventureWorks2014 database.
1 2 3 | USE AdventureWorks2014 GO DROP User DenyLogin |
If you do this in a running system, there will be a short time interruption.
