sql server - Hide SQL database from Management Studio

Question

How can you hide databases you do not have access rights to when logging into SQL Server 2005 / 2008?

Currently if a user connects, they see all the databases on the server, meaning they have to scan though the list to find their database.

Answer 1

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