1

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?

1 Answer 1

6

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

DENY VIEW ANY DATABASE TO [userlogin]

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

REVOKE VIEW ANY DATABASE FROM [public]
5
  • 2
    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, 2009 at 17:18
  • Looks like you'd have to grant view definition for each role on each db for non-dbos.
    – Sam
    Nov 4, 2009 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] Nov 5, 2009 at 4:06
  • why do you need to grant view definition? Nov 5, 2009 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, 2011 at 21:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .