Search results matching tags 'SQL Server 11', 'SSMS', and 'denali'

SQL Server v.Next (Denali) : More on contained databases and "contained users"

AaronBertrand — Fri, 10 Dec 2010 12:34:00 GMT

One of the reasons for contained databases (see my previous post) is to allow for a more seamless transition when moving a database from one server to another. One of the biggest complications in doing so is making sure that all of the logins are in place on the new server. Contained databases help solve this issue by creating a new type of user: a database-level user with a password. I want to stress that this is not the same concept as a user without a login, which serves a completely different purpose altogether. To avoid confusion, I'm going to call these database-level users "contained users."

To create a contained user, you must be in the context of a contained database. So let's create a couple of contained databases:

CREATE DATABASE CDB1;
GO
CREATE DATABASE CDB2;
GO
ALTER DATABASE CDB1 SET CONTAINMENT = PARTIAL;
GO
ALTER DATABASE CDB2 SET CONTAINMENT = PARTIAL;
GO

So with those databases in place, we can create a contained user named Bob in each of these databases. We'll make the passwords the same just to keep things simple.

USE CDB1;
GO
CREATE USER Bob WITH PASSWORD = 'bar';
GO
USE CDB2;
GO
CREATE USER Bob WITH PASSWORD = 'bar';
GO

Now when you try to connect to this server using Management Studio, you might be tempted to just type the user name and password and click Connect. This won't work, because the user Bob is not a SQL login and does not have a default database, so SQL Authentication fails:

Since no database is provided, SQL Server isn't able to check if there is a contained user - the engine is not going to go through the hassle of checking every single database and letting you into the first one that works. However, if you click the Options >> button, on the Connection Properties tab there is a "Connect to database:" option. But when you try to specify the database, the drop-down only offers <default> (which we've already established won't work), and <Browse server...>:

If you choose the <Browse server...> option, it brings up this prompt:

But if you say Yes, the connection immediately fails with the same "Login failed" error as above, because the contained user does not have access to the list of databases on the server.

This is by design. And there is a fix for all of this, if you go back to the Connection Properties tab. While the "Connect to database:" option appears to be a dropdown, it is actually a combo box, so you can type the name of your database, and this will allow you to connect. You should create a registered server with these options so that you only have to perform this action once.

Once you're connected, you'll notice some slightly different behavior in Management Studio. The Object Explorer list is quite sparse - as a contained database user, you don't have access to any of the other databases on the system (except for master and tempdb), or to server-level entities such as SQL Server Agent and the Security and Management nodes:

You'll also notice in a query window that the Use database dropdown list contains only the three databases you see above: the contained database you connected to, as well as master and tempdb. You don't get visibility into the other database you are also "contained" within, even though the username and password match (the sids do not, if you care to inspect the sys.database_principals view in each database). In order to connect to that database, you need to change your current connection to target that database instead (you can do this by right-clicking within the query window and selecting Connection > Change Connection, and following the steps as above).

For fun I tried to see what would happen if I tried to USE the other database:

USE CDB2;

The result:

Msg 916, Level 14, State 1, Line 1
The server principal "S-1-9-3-3958803033-1179633189-3577740975-3121738123." is not able to access the database "CDB2" under the current security context.

[For what it's worth, I think this error message is wrong. It should say "database principal" and it should show the name of the current user, instead of that horribly cryptic identifier. Not that I can't figure out who I'm logged in as, or that it would help me figure out the problem quicker; I just think it could be tidier.]

The same error message occurs if I try to perform a join between the two databases, or try to access a synonym that underneath accesses a 3-part name referencing the other database. This is more or less what I expected, because the user is truly contained to this database, even though there happens to be a user with the same name and password in another database. However, I hope this functionality is enhanced in the future... I believe that it would fit nicely with availability groups if I can fail over multiple databases as a unit, and also bring over contained users that can access any of the databases within the availability group. This seems to be a more real-world scenario than a user that can only interact with and within a single database.

SQL Server v.Next (Denali) : Another SSMS bug that should be fixed

AaronBertrand — Wed, 01 Dec 2010 18:41:00 GMT

Sorry to call this out in a separate post (I talked about a bunch of SSMS Connect items the other day), but Aaron Nelson (blog | twitter) jogged my memory today about an issue that has gone unfixed for years: the custom coloring for Registered Servers is neither consistent nor global.

For one of my servers, I've chosen a red color to show in the status bar. Let's pretend this is a production server, and I want the red to remind me to use caution. I can set this up by right-clicking a Registered Server, choosing Properties, and moving to the Connection Properties tab:

Once I save, a new query window - when launched by right-clicking the Registered Server and choosing New Query - looks like this:

This is a great feature, if only it worked all the time.

For example, if I right-click within the above query window and choose Connection > Change Connection, and switch to a server that doesn't use a custom color (or uses a different color), the status bar stays red. More importantly, if I'm in a query editor for a connection without a custom color, and I switch the connection to my production server, the color does not change to red.

Similarly, if I open a new instance of SSMS and click on New Query (or am prompted because my Startup option includes new query window), the status bar remains the default yellowish color, not red.

These gaps in functionality defeat the purpose of the visual cue, and I am hoping that a fix can be addressed in the Denali timeframe. I wanted to call this one out separately because it can lead to pretty serious errors.

There are two Connect items about this issue; one has been closed as "Won't Fix" and the other (which came earlier!) has yet to see a response from Microsoft. I'm hoping that with enough steam we can change that: please vote and comment!

Connect #533667 : SSMS Connection Colouring Not Implemented Sensibly
Connect #504418 : [SSMS] Make color coding of query windows work all the time

There are workarounds, of course: always use Registered Servers to launch your query windows, use Mladen's free SSMS Tools Pack, be a lot more careful, etc. etc. But personally I feel that such a useful built-in feature should be rectified to function reliably.

SQL Server v.Next (Denali) : Some Management Studio bugs you should vote for

AaronBertrand — Mon, 29 Nov 2010 18:14:00 GMT

It's been a while since I hosted a true Connect ~~pity party~~ digest, trying to pimp your votes for the issues I think are worthwhile. And okay, some of these bugs haven't been filed in the Denali time frame - but those are still in Denali's version of SSMS. And since we are not expecting a release for a year, and the next CTP won't be here until early 2011 sometime, what better time to build some momentum and hopefully sneak some of these fixes in? It won't happen without votes, so please check out any that you can appreciate.

The "Change Connection" dialog

#542304 : Change Connection should order instance names in a better way
It would be great if we could customize this, but I'll take at least a defined and predictable sort order, regardless of what they choose. Alphabetical seems to make the most sense to me, though others (such as @AdamMachanic) might argue that most recently connected would be more logical.

#424800 : SSMS : Expose "Connect to Server" MRU list to users
The most important problem here is that you can have duplicates in the list - one for Windows Authentication, and potentially one for each SQL Authentication login you've used. So when you pull from the list again, it is trial and error to pick the right server + credential combination - like Forrest Gump not knowing which chocolate he's going to get. What I've requested here is a way to see and prune this list (and by extension, possibly even re-order it, addressing Adam's point above). Don't be fooled by the bug's current status - it says "Closed as Fixed," but the last Microsoft comment (from November 18) clearly states: "We will be [sic] try to fix this bug in next major release..." - which doesn't sound too fixed to me just yet.

#621507 : [Denali SSMS] : Prompt about previously registered servers is too sticky
While not completely related to the Change Connection dialog, this prompt comes back for me every time I try to change a connection by right-clicking within a query window. It also keeps coming back every time I launch SSMS, no matter how many times I check the "Do not show this message again" checkbox. For other users (e.g. @SQLSoldier), they can't get this prompt to show up at all.

Keyboard shortcuts

In the move to the VS shell, we have lost a LOT of keyboard functionality that we were used to in prior iterations of SSMS, and gained a few undesirable behaviors as well.

#616721 : SSMS Short Cut key "CTRL + E" is not working to execute any query
Along with Ctrl + N, this functionality is missed by many users. Thankfully, it seems this bug will be fixed for the next CTP.
#614413 : [Denali SSMS] CTRL+U keybaord shortcut no longer works
Ctrl + U stopped working because of the next item: the keyboard shortcut for lower case (Ctrl + Shift + L) was reassigned. Jamie probably didn't notice the new functionality of Ctrl + U because he didn't have any text selected at the time (or it was already lower case).
#625257 : [Denali SSMS] : Ctrl + Shift + L no longer issues lower case
As described above - this keyboard shortcut was inexplicably changed to a very unintuitive key combination. Yet Ctrl + Shift + U still handles upper case.
#623863 : [Denali SSMS] : Ctrl + Shift + M no longer raises template parameter dialog
This keystroke omission practically destroys the functionality of templates.

#625260 : [Denali SSMS] : F5 in Object Explorer no longer refreshes; triggers debug instead
"Start debugging" is supposed to be triggered by Alt + F5, not F5 on its own. And I shouldn't be able to start debugging when I am focused in Object Explorer anyway.
#623498 : Error when pressing the F1 key in SSMS
In older versions of SSMS, pressing F1 without focus on a relevant object would just be a no-op; now it yields a pretty badly-handled error.

Just plain old inconsistency, or promoting bad practices

#623865 : [Denali SSMS] : Inconsistency : Non-Clustered vs. Nonclustered
This is just a simple inconsistency that should be easy to correct, but they're already making excuses about why they can't correct it in the next 12 months.

#623864 : [Denali SSMS] : Inconsistency : Is it Template Explorer or Template Browser?
Another simple inconsistency that should be easy to fix. I think it's just a case of the left hand not knowing what the right hand is doing.

#622784 : [Denali SSMS] : Proliferation of the use of deprecated system tables (e.g. sysobjects)
There should be a standard way to prevent the dev team from implementing code that uses deprecated objects. It's kind of embarrassing when we spread the word that you should stop using sysobjects in your scripts, and then Microsoft goes along and keeps using it...

#623047 : Incorrect SET options for New Service Broker Application
As above, they need to follow better standards and be on the same page as far as scripting conventions and defaults.
#623049 : master misspelled in new Service Broker tasks
All code that gets called within or generated by Management Studio and other shipped components should be tested on a case sensitive collation.

Poor behavior

#625266 : Denali SSMS Incorrectly Reports session_id for connected tabs
Pretty scary that SSMS can get into a confused state and not know which SPID is which. I believe the symptom is related to SSMS losing connectivity with the server, whether it be because of a network blip of some kind or because the service restarted or failed over.
#615766 : [Denali SSMS] : Execution plan tooltip causes very slow behavior
I see this bug constantly - I swear every time I accidentally hover over the query portion of an execution plan. I have showed one of the program managers - in person - how to reproduce this bug, so I expect that it will get corrected (preferably just by eliminating the useless tooltip altogether).
#623735 : Denali CTP1 SSMS Error Message Click doesn't return to error line
This is a behavior that is sorely missed. It is currently non-functional because of the transition to the Errors List that Visual Studio developers know and love (I talked about this a bit at the bottom of an earlier blog post about "the new SSMS"). I hope there is middle ground here somewhere.

#425661 : SSMS : unable to register local servers
This bug has been around for a long time, but Microsoft continuously insists that it can't reproduce. If you have any further evidence of this problem, please comment / vote / validate!

Toolbar customization

#615154 : [Denali SSMS] : Can no longer drag buttons within or off the toolbar
While most of the T-SQL developers out there are clamoring for better debugging support in Management Studio, I am still "old school" and want nothing else but to remove the Debug icon from the toolbar. I've found that customizing the toolbar has become a lot more cumbersome in Denali.

Debugging

I mentioned four Connect items about debugging in a post a few days ago. Rather than repeat them here, I'll just point you at that blog post.

Debugging T-SQL in SQL Server v.Next (Denali) : You can make a difference!

AaronBertrand — Wed, 24 Nov 2010 19:23:00 GMT

This afternoon, Adam Machanic (blog | twitter) reminded me how the current T-SQL debugger in Management Studio is ineffective. Sure, there are some enhancements coming in Denali (I mentioned them briefly yesterday), but there are some real key elements missing. One of the most glaring omissions is the ability to see the contents of #temp tables and @table variables during a debugging session. For stored procedures that can take a long time to run, it is quite cumbersome to edit the procedure to dump the contents of the #temp table as a resultset, which can obviously interfere with the operation of the procedure (especially if applications currently rely on its interface). As Adam eloquently stated, "All I want right now is a debugger that helps with real challenges. Scalar variables are not enough."

Hear, hear. This is one of the reasons I have yet to step back from my curmudgeony debugging methods: PRINT, RAISERROR, and (since 2005) TRY/CATCH. I don't come from an OOP background, but I do understand why many people value a more formal method of debugging.

So, how can you help? Well, I know some of you do not believe in Connect, but for those that do - this issue of not being able to see into #temp tables and @table variables is covered by no fewer than four Connect items. I am asking that you go and vote for all four of them, because Connect doesn't have a good way for us peons to close duplicates and focus on a single item. (I'd love to pick just one and tell you to vote there, but with my luck, Microsoft will choose a different one to keep alive, if they ever get around to consolidating.)

Connect #623353 : View contents of table variables and temp tables in SSMS debugger
Connect #582167 : Debugger should hyperlink into CTE-s, table variables, temp tables and changed rows
Connect #454870 : No way to see contents of table valued variable in debugger
Connect #363054 : temp table and table variable visibility through debug

So please, if you value debugging and you want to see better improvements than just being able to watch scalar variables, vote on these items. And if someone tells you they're going to create a new Connect item in a similar vein, please tell them to search first. Having multiple items covering the same issue just spreads out the votes and makes it less likely that the issue will be tackled. Thanks!