Need VIEW DEFINITION permissions per database

Need VIEW DEFINITION permissions per database by Erland Sommarskog

Active

109

Type:	Suggestion
ID:	273830
Opened:	4/26/2007 3:08:24 PM
Access Restriction:	Public

Workaround(s)

view

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 (expand)

English

SQL Engine

Add a server permission VIEW DEFINITION to apply to a database. Maintaining it on its own would not be very fun,
and users would expect automatic update of the state,
as users are added or dropped. This can certainly get really
tricky when databases are restored or brought online. Maybe there could be a background task to handle this.

But even a difficult VIEW DEFINITION per database would be
worthwhile for security-minded DBAs, and would make it possible to extend the philosophy for other objects to extend to databases.

Improved Security

0 attachments

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.