Need VIEW DEFINITION permissions per database - by Erland Sommarskog

Status : 


Sign in
to vote
ID 273830 Comments
Status Active Workarounds
Type Suggestion Repros 7
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 vstrien 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 侨医 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.