Connect Digest : 2012-01-09
January 9th, 20121
Connect Digest : 2012-01-09
January 9th, 20121

Hide databases from users who shouldn't be able to see them

This is a long-standing request from Erland Sommarskog which I've highlighted in previous digests. But the underlying problem keeps coming up in multiple venues, so I thought it would be good to call attention to the item one more time. Some will argue that the contained database feature provides a solution for this, but that only works well if you want to restrict a user to exactly one database, and only works well if your application is compatible with the limitations of the feature. Please comment on the item and explain how this feature will help you in your environment.

     #273830 : Need VIEW DEFINITION permissions per database

Contained Database users are people too

In playing with the contained database feature as a solution to Erland's concern above, I discovered an unfortunate bug: a database-level user (with password) who has connected to their contained database using SSMS will not enjoy most of the important IntelliSense features. I'm highlighting this Connect item not so that you can vote for it, but rather just to be sure you're aware of this limitation if you intend to utilize contained databases in the short term. As an side effect, I also discovered that there doesn't exist a straightforward way to set up a contained user that can bypass the password policy in place, unlike server-level logins (where you can say CHECK_POLICY = OFF). Personally I think they got this backwards – logins are the security entity where you want to make it harder to implement simple passwords. If you want a contained user with a simple password, you can create a server-level login, associate it with a database user, and then use sp_migrate_user_to_contained (note that I haven't tried this).

     #717063 : SSMS : IntelliSense does not function for a contained user

     #717069 : Contained User syntax does not support bypassing password policy 

Please just go parallel, regardless of other factors

Paul White (@SQL_Kiwi) has asked for an option that is kind of the opposite of MAXDOP. I say "kind of" because he doesn't want to be able to say MINDOP x, but rather try to coerce the optimizer to use a parallel plan and then follow the same rules it normally would in determining the level of parallelism. 

     #714968 : Provide a hint to force generation of a parallel plan

Expose SHOW_STATISTICS through a DMV

Greg Low has proposed adding a DMV that would mirror DBCC SHOW_STATISTICS output, making it easier to work with the results. I'm all for this, as it can be quite a hassle to mix monitoring queries with DBCC calls.

     #611155 : DBCC SHOW_STATISTICS info should be available as a DMV

Check constraints during CHECKDB

Thanks to Ola Hallengren, they are considering adding the ability to check all constraints (and, where appropriate, mark them as trusted) as a part of the DBCC CHECKDB process (specifically, using the EXTENDED_LOGICAL_CHECKS option). There are already plenty of votes, but more votes (and, more importantly, comments about how this will help in your environment) will help.

     #508837 : Option to check constraints in DBCC CHECKDB

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

1 Response

  1. Justin Dearing says:

    Regarding CHECKDB option, I'm of the understanding that CHECKDB options are being deprecated in favor of ALTER TABLE/ALTER INDEX. While its annoying to not be able to do something on an entire database, its the decision Microsoft has made.