Need VIEW DEFINITION permissions per database - by Erland Sommarskog

Status : 


Sign in
to vote
ID 273830 Comments
Status Active Workarounds
Type Suggestion Repros 21
Opened 4/26/2007 3:08:24 PM
Access Restriction Public


A common question on the newsgroups is "What do I need to do so that users in Mgmt 
Studio only see the databases they have access to?". Surely, they are accustomed to
this from SQL 2000, where EM would behave this way. Alas, there were issues with
how EM implemented this. It went and checked every database, and this could be 
costly, particularly if there were many databases on auto-close.

What is possible in SQL 2005, is to revoke or deny the permission VIEW ANY DATABASE.
However, this is too far-reaching, because now users can only see the system databases
and databases that they own.

The current situation is certainly unsatisfying, and it also goes against the principle
"secure by default".

The root of the problem is that the answer to the question whether the user is 
permitted in a database is found only in the database itself; not in master.

Sign in to post a comment.
Posted by echoDreamz on 1/18/2018 at 3:06 PM
Come on Microsoft. 11 years almost... We need this to keep customers from seeing other databases that they are not db_owner of. Would help with all the calls of why can I see other's databases? The single user dbo option is not really an option as most customers want multiple users.
Posted by taffit on 12/11/2017 at 11:40 PM
+1. Unacceptible to hide all the databases or make a single user the owner of a database. And even if developers can simply use a "USE [database]", the business user with Excel and its wizards can not.
Please fix this. Urgently.
Posted by SQLBrander on 11/21/2017 at 5:00 AM
Still no solution for this? There is a workaround by making a user DB_OWNER but that is not acceptable. Please fix this.
Posted by MarcenfaaMym on 9/28/2017 at 8:41 PM
This is needed specially in today's SaaS companies. Can you adapt to the change by allowing this layer of security? This seriously is a small silly fix even us Enterprise users should have access to.

Thanks please!
Posted by tripleog88 on 4/11/2017 at 5:15 PM
Our organization would like this for our SaaS environment. We want to be able to DENY VIEW ANY DATABASE to a login but allow them to view any databases where they are assigned the db_datareader role; this should be done without having to make a db_datareader login a database owner or assigned db_owner.
Posted by mssystech on 3/3/2017 at 11:56 AM
On a SQL Server instance with multiple databases, we would like to allow a user to view only their database WITHOUT having to make that user DBO. In multitenant environment with many databases on SQL instance , it is important for us per security policy that users are not allowed to see other databases in SSMS. There are many users those need read only access to database and only should see the databases they have access to. DENY VIEW ANY DATABASE TO <USER> or DENY VIEW ANY DATABASE TO PUBLIC , not helping to achieve this requirement in SQL. Looking forward to have this functionality added in SQL at the earliest. Thanks
Posted by Bryan Ellis on 7/30/2016 at 8:24 AM
My situation is that I have a hosted SQL DB and to get to it in the tree view of SSMS I have to wait on 100's of other databases to load to which I have no access whatsoever. I can immediately do a New Query and it defaults me to my database. Would be great if SSMS would limit the listing to those the user has access to.
Maybe caching the db-specific information in an in-memory view at startup then including triggers to update whenever the DB permissions change would be useful. SSMS could then query this internal view instead. Seems like it would address the "root" of the problem. I think in most production situations the permissions change seldom enough that this would not be a bad solution.
Posted by PhantomMan221 on 6/29/2016 at 2:39 AM
The fact this problem has no workarounds should make this a high priority fix !!!
Posted by G. Brandwijk on 1/20/2016 at 7:52 AM
Please make this a priority!

I know that it is difficult because the permission rights are saved in the database which costs a lot of performance, but it would already be very good when there is something like "Add existing database to my list" option. So when the login user knows what the name of the database is (but not listed because he has only the role db_owner), he can create his own list with databases where he has rights.
Posted by WiVM on 12/8/2015 at 10:07 PM
Not working in 2014...
Posted by Chief RuleMaker on 6/6/2015 at 6:43 PM
Why has this not been addressed by Microsoft - after being pointed out first in 2006! Now with the explosion of Hyper-V and Cloud Services, HOSTING Databases as SAS is greatly in need of this "feature". Management Studio is allowed to be installed onto almost any computer and allowed to connect to any SQL Server that they can connect to. Here is where the problem is - unless I make the User "DBO" (and I can only make ONE), they cannot see any database in SSMS if I have set the DENY VIEW ALL DATABASES! There MUST be a GRANT VIEW DATABASE <DBName> to <User> in order for ANY User to be able to use their "ASSIGNED RIGHTS" in ONLY the Databases that they are given ANY rights to. When will this be fixed???
Posted by deester1 on 4/30/2015 at 3:35 PM
Given the higher degree of security needed on SQL Servers, users/logins that are NOT the owner should only be allowed to see the databases for which they have been granted permissions. A new item should be added for permissions that would grant view only specific databases.
Posted by Koos van Strien on 2/20/2015 at 3:58 AM
When using Power Pivot, this feature is even more desirable: displaying only the relevant databases to a user greatly enhances the ease of use (ESPECIALLY when users see 'master' in the database list, they often think 'that is the one I need')
Posted by Yousef Ekhtiari on 9/11/2014 at 2:10 AM
Has this security bug fixed in SQL server 2014?
Posted by EricTN on 7/7/2014 at 12:16 AM
Lacking other solutions as requested in these comments, I'd be happy if I could just restrict the list of databases based on a Filter setting, the same thing Object explorer has enabled within databases for Tables, Views, Stored Procedures, etc. The Filter fly-out would be on the context menu for the Databases node of the Object Explorer tree view.
Posted by Erland Sommarskog on 6/28/2014 at 1:26 AM
The other day, I started to think: how difficult would it be to track the information in master? sys.database_principals is simply
replicated to master on CREATE/DROP USER, attach/detach/RESTORE. From a user perspective, this is the most palatable solution as it requires no
work beyond revoking VIEW ANY DATBASE from public. It may seem that there could be some hairy transactional issues when doing for instance RESTORE - SQL Server needs to get rid of the users from the database that was overwritten and then add users from the restored database, but it has not be synchronous; a background job could take care of this.

Whatever, it is a shame that this has not been addressed. No, Microsoft, such a feature will not sell you any new licenses, but I'd say that you
owe it to the people.
Posted by RyanKelley on 10/25/2012 at 12:55 PM
This is a must have in a shared/SaaS environment where users are allowed to connect to the database server directly with reporting tools, They should not be able to see a list of all the databases on the server, just theirs.
Posted by MattOARRT on 4/5/2012 at 7:52 AM
Jon Morisi, apparently not. It's unfortunate as this does really go against the principle of "secure by default". Not sure what to tell our PCI auditors about this one.
Posted by Jon Morisi on 3/21/2012 at 11:07 AM
5 years...Guess it's not getting implemented?
Posted by GP Van Eron 万侨医 on 1/13/2012 at 7:46 AM
Kindly "seriously" take this into account for SQL 2012. Not being able to restrict users visibility to only the database in which access was provided in SSMS is more than a major inconvenience, but a security concern in my view.    I do want users logging into SSMS to even know that other databases exist that they are not explicitly given access to.    


Posted by Gaurav 17 on 1/2/2012 at 3:50 AM
Any idae on this? Should we expect this to be fixed?
Posted by MSP858 on 8/3/2011 at 7:12 AM
Has there been any new development for this suggestion? As an organization, we'd like to see this happen in the future as we're deploying SSMS as the developer tool for all SQL projects. Limiting access to what the developers see(as in, only their database) would be a big benefit for us as we will not have the need to purchase an external tool to achieve this feature(aka Toad for SQL Server).
Posted by Microsoft on 9/23/2010 at 4:12 PM
The problem with changing SSMS is that it doesn't strictly fix the issue as anyone who is determined to discover the other databases would just directly query the server metadata. As such, I think we need to investigate a solution this for all cases, not just SSMS.

Posted by Mark Guinness on 6/11/2010 at 12:51 PM
Surely a change could also be made to SSMS as well? When it parses master.sys.databases it could conceivably filter those results. Whilst not a foolproof method, at least it would work as a quick temporary solution (considering this was posted 3 yrs ago).
Posted by Microsoft on 4/6/2008 at 6:30 PM
Hi Erland,

Thank you for your suggestion. We will investigate a solution for a future version of SQL Server (post 2008).

Il-Sung Lee.