I have installed sql server express 2008 and also sql server management studio. I've successfully configured remote connectivity and created a database user and password.
my problem is that in the remote sql server management studio – i want the user to only see the database i assign to them and not see System Databases
I've noticed that the sa login also appears in the folder tree in my remote session which i do not want
How do I go about hiding the system databases as well as logins so that the remote connection only sees logins and database relevant to them
Best Answer
By default, all logins can see all databases. This is done through the "public" role. To change that, you just have to revoke the permission from the public role.
However, master and tempdb will always be visible to the public role. See here for more information: VIEW ANY DATABASE Permission
As far as logins is concerned, each login can see the logins on which has been granted permissions.