Um blog sobre nada

Um conjunto de inutilidades que podem vir a ser úteis

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”

clip_image001

 

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.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s

 
%d bloggers like this: