3

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

Improve this question
5
  • My question is why do these users have access to have SSMS installed and connect to your sql server if you don't want them to see certain things??? It seems that the other databases should be on a different server or those users have absolutely no business connecting to a sql instance in the first place.
    – Sean Lange
    Commented Dec 11, 2014 at 20:30
  • @SeanLange - As I'm unfortunately finding out with a current integration... some companies host multiple clients on one instance and they do not want those clients to be able to see their data. -_-
    – Bonez024
    Commented Apr 23, 2019 at 13:20
  • 1
    @Bonez024 then they should NOT be allowing them access to the sql server. To solve this see the answer by SpaghettiDBA
    – Sean Lange
    Commented Apr 23, 2019 at 13:31
  • @SeanLange - I do not disagree with your statement, this is causing me quite a headache right now with our current situation. What I am doing is stating a use case that (loosely) lends merit to this configuration. The provider we're working with has an architecture that wants to disallow the viewing of DBO's altogether, and only allowing DB queries against DB's we have permission to.
    – Bonez024
    Commented Apr 23, 2019 at 16:25
  • What I really want to know is why Microsoft doesn't allow you to set this by DB. Why does it have to be across Every DB?
    – Bonez024
    Commented Apr 23, 2019 at 16:26

2 Answers 2

Reset to default
3

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.

REVOKE VIEW ANY DATABASE FROM PUBLIC

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.

Improve this answer
2
  • "However, master and tempdb will always be visible to the public role" Why is that so? Is there a particular reason?
    – Mattia Nocerino
    Commented Mar 19, 2018 at 11:57
  • 1
    @MattiaNocerino because the documentation says so :-) Specifically, tempdb stores temporary objects (not only temp tables, but also sorts , hashtables and other types of temporary objects) so it always has to be available. Master contains system objects that have to be available, in the same way.
    – spaghettidba
    Commented Mar 19, 2018 at 12:01
3

You can hide the system database in SQL Management Studio following Way that I have mentioned below.

Navigate Tools-->Options-->Environment-->General and choose "Hide system objects in Object Explorer".

OR

In the SQL Server Management Studio, Right click the server and click "Properties". Click on "Permissions" and then select the "Public" role and remove "Grant" from "View Any Database".

Or You can use 

USE <YourDatabase>

ALTER AUTHORIZATION ON DATABASE::<YourDatabase> to <Login>

USE MASTER

DENY VIEW ANY DATABASE TO <Login>
Improve this answer
1
  • This is a client-side option that has to set on all the clients and can also be easily reverted to the original behavior.
    – spaghettidba
    Commented Dec 11, 2014 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.