Home Dashboard Directory Help
Search

Need VIEW DEFINITION permissions per database by Erland Sommarskog


Status: 

Active


159
0
Sign in
to vote
Type: Suggestion
ID: 273830
Opened: 4/26/2007 3:08:24 PM
Access Restriction: Public
0
Workaround(s)
view

Description

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.

Details
Sign in to post a comment.
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.    

Thanks

GP
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.

Il-Sung.
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.
Sign in to post a workaround.