A database has hundreds of possible permissions (Create table, Create View, etc…). One of those permissions is to actually VIEW the database, such as when you log in to a tool like SSMS.
By default when Microsoft SQL server is set up, all users get created with the public role. This role has access to VIEW all databases on the server.
While the user can not view, delete, or edit anything with just this public role permission set some might not like the ability to view the available databases on the server for security purposes. Removing these permissions is extremely easy, which we’ve provided instructions for doing so below:
Note: On our shared server environments we have these VIEW permissions disabled so that only the users set with proper permissions as DBO (database owner) are able to see the database.
Disable view permissions for public role
After disabling view permissions for the public role only system administrator users can see all databases on the server. Any user set with DBO permissions to a database will have the ability to see those databases they are assigned to with DBO permissions.
Login to SQL Server Management Studio with an administrative user (such as the default ‘sa’ user).
Click on the option for New Query
Run the below T-SQL query:
USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO
Need to re-enable view permission for public role?
If you end up wanting to revert back to the default permissions of allowing users to view all databases on the server, then you can run the below to revert:
Login to SQL Server Management Studio with an administrative user (such as the default ‘sa’ user).
Click on the option for New Query
Run the below T-SQL query:
USE MASTER
GO
GRANT VIEW ANY DATABASE TO PUBLIC
GO