Show only a few databases on SSMS
Posted by Diego em Novembro 21, 2012
This comes from the situation where you have an instance with several databases, maybe all of them almost equally named, like a development server where each user has its own copy of the DB, for example, MY_APPLICATION_DB_USER_01, MY_APPLICATION_DB_USER_02, MY_APPLICATION_DB_USER_03 and so one. It may come a time when you are just interested on your own databases and don’t want to keep looking on a huge list to find it, so how to display only a few databases on the SSMS database list?
The idea is to deny the user the ability to view all databases and then, make this user db_owner of the databases he is supposed to see. Of course, since a database can have only one owner, with this technique, it will be visible to only to its owner so you might want not to use your regular user (probably your AD one) to do this and create a new SQL User.
The steps are:
1) Create a “SQL Server Authentication User”, in this example, called just Diego
2) Right Click on your instance, select Properties -> Permissions
3) Select your user and deny “view any database”
As the name says, this setting prevents the user of seeing any database on the instance. Exceptions are any database that he owns.
4) Right click the database -> Properties -> Files and change the owner to your user.
Now, you can log in with this user and see only the DBs you actually need to see.