6

I'm new to MS SQL and asking this question as a web developer with limited sysadmin experience who desires his service providers to use best practices.

We have one database on a remote MS SQL server provided by our hosting company. Other clients of our hosting company use this same server for their databases. When I log in to the server using Microsoft SQL Server Management Studio Express, I can see a whole lot of information, including the names of all of the databases on the server and all of the logins.

While I can't access other clients' databases nor see passwords, it seems strange to me that I would even be able to see this information. I certainly don't want other clients to know the name of my database or my login. When I brought up this concern with my service provider, this was his reply:

I checked with my SQL guy and he said that unfortunately is a limitation of MSSQL Server. Although the database names are visible, you cannot access any database without the password for that database.

This is what I see after logging in:

  • mssql.server.com
    • Databases
      • System Databases
      • A whole bunch of client databases...
    • Security
      • Logins
      • A whole bunch of client logins...
    • Server Objects
    • Replication
    • Management

Is this the right way to set up an SQL Server? Please confirm or deny my suspicion that the answer is no and I need to find someone else to host our database.

nselikoff
  • 163
  • 1
  • 1
  • 3

5 Answers5

4

Speaking from the development side, I can say that the admin should be able to lock everyone down to their own database (and probably master as well, for metadata). From the MSDN, the list of SQL Server securables includes databases and the permissions include being able to even see the database. Here is a link to the MSDN Security considerations for databases.

Tom A
  • 218
  • 1
  • 3
  • 10
3

Management Studio gets the list of databases from sys.databases. The default permissions on sys.databases is for each login to see only it's own database:

If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.

If you have logins that see databases other that their own, it implies said logins have unnecessary privileges, like CREATE DATABASE.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
2

To hide databases to all LOGINS, remove/revoke "VIEW ANY DATABASE" permission from the public server role

http://sqlism.blogspot.com/2014/10/preventing-logins-and-users-to-see.html

  • 1
    There are so many mistakes in this thread including the answer which was selected. This is actually the only good answer. All LOGINs belongs to a server role named "public", and USERs inherit the permissions granted to public rule. If you want to grant or revoke permissions to all new logins then you can edit the permissions of the public rule. For the current task you can do it using the SSMS GUI: open 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". – Ronen Ariely May 19 '20 at 01:58
0

Using MS SQL this way as a shared host doesn't strike me as desirable at all - but you get what you pay for. If your terms and conditions of your contract don't say that you get your own SQL instance, and only get a database in a shared instance, then you are indeed getting what you pay for.

mfinni
  • 36,144
  • 4
  • 53
  • 86
0

This hides other DB's and users but only for a single user (probably what you're after anyway). I've tested it on MS SQL 2008 R2.

In Server Management Studio:

  • Create a user account, don't set any permissions
  • Open the server properties > Permissions > select the new account > check the "Deny to view databases" box
  • Open the database properties > Files > set the DB owner to the user account
Molomby
  • 232
  • 1
  • 7