Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

1 Answer 1

up vote 6 down vote accepted

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]
share|improve this answer
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

 
discard

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.