Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

have a web hosting service that is using MS SQL Server 2005 Express Edition. I have enabled remote database access , but I want to set permission for users so that when they login to Management studio they just see their database not all databases that I have on server. How can I set this up?

share|improve this question
up vote 6 down vote accepted

deny the 'view any database' permission to the login.


To do this for all logins into the database server, then revoke the view any database permission from the public role

share|improve this answer
I tried this out on our dev server and people were not able to see databases they had access to, just dbs they OWN. – Sam Nov 4 '09 at 17:18
Looks like you'd have to grant view definition for each role on each db for non-dbos. – Sam Nov 4 '09 at 17:19
yes if you revoke view any database from [public] you will have to grant the permission to every login. You can put it back with GRANT VIEW ANY DATABASE TO [public] – Nick Kavadias Nov 5 '09 at 4:06
why do you need to grant view definition? – Nick Kavadias Nov 5 '09 at 4:07
@Sam but 'view definition' doesn't let people see the database when connecting with SSMS after REVOKE VIEW ANY DATABASE FROM [public] – friism Jan 30 '11 at 21:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.