up vote 1 down vote favorite
2
share [g+] share [fb]

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?

link|improve this question
86% accept rate
feedback

1 Answer

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]
link|improve this answer
1  
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
feedback

Your Answer

 
or
required, but never shown
discard

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