How to hide databases in SQL Server Management Studio from unauthorised users?

Question:

If a user is not authorized to see a database can I exclude that database from even appearing in SQL Server Management Studio for that specified user or group?

Answer:

Until SQL Server version 2000 it was not possible to hide the database information from being displayed on SQL Server Enterprise Manager. In SQL Server 2005 it is possible with a new server side role that has been created. VIEW ANY DATABASE permission is a new, server-level permission. A login that is granted with this permission can see metadata that describes all databases, regardless of whether the login owns or can actually use a particular database. Please note By default, the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server 2005 can see all databases in the instance.

An interesting point to note is that being a member of db_owner is not sufficient to see the database if “view any database” was denied. In this regard SQL Server Development team are working on new features in order to make this more affective in future releases.




Related Articles :

  • No Related Articles Found

One Response to “How to hide databases in SQL Server Management Studio from unauthorised users?”

  1. The answer given seems to be to a different question.

    Q: Is it possible to hide databases from unauthorized users?

    A: It is possible to show all databases to all users.

    Or, more specifically “Therefore, by default, every user that connects to an instance of SQL Server 2005 can see all databases in the instance.”

    So the answer is no? No databases can be hidden from any users?

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |