Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

5 Answers 5

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.