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.– SamNov 4, 2009 at 17:18
-
Looks like you'd have to grant view definition for each role on each db for non-dbos.– SamNov 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] Nov 5, 2009 at 4:06
-
-
@Sam but 'view definition' doesn't let people see the database when connecting with SSMS after REVOKE VIEW ANY DATABASE FROM [public]– friismJan 30, 2011 at 21:01