Hide SQL database from Management Studio

Question

After hours of trying to figure out how to create a user account which only has access to 1 DB, and can only see that DB. I think i figured it out!!!!

1. Create a user account ( make sure its not mapped to any Database, otherwise you will get the final error Msg 15110, Level 16, State 1 and note proposed solution)

   USE [master]
   GO
   CREATE LOGIN [us4] 
       WITH PASSWORD=N'123', 
       DEFAULT_DATABASE=[master], 
       CHECK_EXPIRATION=OFF, 
       CHECK_POLICY=OFF
   

2. Right Click on the upper section of the SQL (SQLSERVER Name)>Properties>Permissions>Click on the user account, and select Deny to view databases.

   use [master]
   GO
   DENY VIEW ANY DATABASE TO [us4]
   

3. Right Click on the newly created DB, Properties,Files, and change the Owner to the newly created account.(important note: ALTER ROLE [db_owner] ADD MEMBER [us4] does not work)

   USE [dbname]
   GO
   EXEC dbo.sp_changedbowner @loginame = N'us4', @map = false
   

At this point, once the user logs in he will see the Master,tempdb and will also see the new DB which he is a DB Owner of..You may want to go to Tools>Option and enabled the option to hide system objects so that you don't show the master,tempdb,etc. You may also need SP1 if this option does not work

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database.

proposed solution to Msg 15110: to resolve above error simply delete the user from database security node and try again

Hope that helps...

Nikhil

This actually won't work the way that makes sense or that you might expect that it would.

You REVOKE VIEW ANY DATABASE from the public role, but then the user has to be the database owner of the database or it can't be seen, but it still can be accessed.

The problem is a Database Engine Security shortcoming and not likely to be fixed in the current or future release of SQL Server.

Erland Sommarskog opened the following connect item for this a while ago, and it recently was discussed on twitter and with Microsoft by the SQL MVP's.

Vote for the connect and help make it more of a priority for Microsoft to fix:

Connect Feedback

Basically the permissions are stored at the database level, so it would require enumerating each database to determine if the user has connect rights to display the database in the object explorer, which is an expensive task to perform and how the older EM used to do things.

The proposes solution is for this information to be maintained at the server level as well, which is a major change.

You would need to revoke the permission 'VIEW ANY DATABASE' from the role PUBLIC (SQL SERVER 2005 onwards)

Add user to DB as Db owner after removing VIEW ANY DATABASE rights

This will show only the database owned by the login in SSMS.

USE master; GO

DENY VIEW ANY DATABASE TO [loginname]; GO

USE [your db]; GO

DROP USER [loginname]; GO

USE master; GO

ALTER AUTHORIZATION ON DATABASE::[your db]TO [loginname]; GO

Note: this requires the login to exists already