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?

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]
  • 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 '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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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