Connect Digest : 2012-01-09

AaronBertrand — Mon, 09 Jan 2012 14:50:00 GMT

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

Newly closed Connect items auger well for SSIS in Denali

jamiet — Fri, 10 Sep 2010 11:56:00 GMT

Todd Mcdermid spoke recently on his blog post Integration Services vNext Coming Soon about how some recently closed Connect items had encouraged him as to the future of SSIS. Also, Matt Masson from the SSIS team has written a similarly encouraging blog post about some upcoming SSIS enhancements at Upcoming Product Changes.

Now its my turn. Just this morning I had three Connect items returned to me as "fixed" and, like Todd, I'm delighted to see these items getting closed as such. I have long complained about the shortcomings of SSIS's logging framework as I don't believe it produces enough "context" as to why a container happens to be executing; the consequence being that we end up getting lost in a plethora of log records where we can't see how each one relates to another. These three Connect items give me hope that this issue is getting addressed in the next version of SSIS (aka SQL11 aka Denali).

The three Connect items in question are:

Can't differrentiate between multiple instances of a task running in parallel - If a container happens to be running in parallel with another instance of itself (e.g. a dataflow task in a package that has been called from two Execute Package Tasks) there is no way to differentiate between the two. A solution would be to provide an Execution identifier for the execution of each container just like the ExecutionGUID that we get for a package.
Please put ExecutionGUID property on DtsContainer - Pretty much the same as the last one
SSIS: Make container stack available - This refers to what I call the context of a task being executed. We know that a task is executing but what are all the ancestral tasks and containers in the container hierarchy that have led to that task being executed?

The provision of more execution metadata for logging purposes gives me hope that the next version of SSIS will have a much better story around logging. Hopefully we won't have to wait long to find out.

@Jamiet

Search results matching tags 'SQL Server 2012' and 'Connect'

Connect Digest : 2012-01-09

Newly closed Connect items auger well for SSIS in Denali