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?

Ashian
  • 400
  • 1
  • 7
  • 24

1 Answers1

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]
Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • 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 04 '09 at 17:18
  • Looks like you'd have to grant view definition for each role on each db for non-dbos. – Sam Nov 04 '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 05 '09 at 04:06
  • why do you need to grant view definition? – Nick Kavadias Nov 05 '09 at 04: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