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
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] 
- 2I tried this out on our dev server and people were not able to see databases they had access to, just dbs they OWN.Sam– Sam2009-11-04 17:18:43 +00:00Commented 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– Sam2009-11-04 17:19:56 +00:00Commented 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]Nick Kavadias– Nick Kavadias2009-11-05 04:06:19 +00:00Commented Nov 5, 2009 at 4:06
- why do you need to grant view definition?Nick Kavadias– Nick Kavadias2009-11-05 04:07:06 +00:00Commented 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– friism2011-01-30 21:01:44 +00:00Commented Jan 30, 2011 at 21:01