One of the things I’ve been searching for is to hide the list of databases a user doesn’t have access to. By default, when a user connects to SQL Server, especially a shared instance, he sees all the databases available on the server, even though he doesn’t have access to view or access any of them. The goal is to only show a user the database(s) he should see. This isn’t fully supported yet by SQL, but there’s a way to solve the problem for the majority of cases.
First, you need to deny the ability for a user to view any databases he does not own.
use master GO DENY VIEW ANY DATABASE TO [myuser]; GO
Secondly, you need to ensure the user is the owner of each database he needs access to. This is the part that’s the workaround, since the user can still access all the databases he’s been assigned permissions for, but only databases he owns show up in the Object Explorer. To change the database owner, you can run the following:
use [MyDatabase] GO EXEC sp_changedbowner 'myuser' GO