I've been using Visual Studio 2005 for over a year, since before the alpha version came out. Recently, I've had occasion to use VS 2003 and confirm what I thought I'd noticed. At first I thought it was an "alpha anomaly" that would be fixed, now I'm not sure.

Has anyone else noticed the autocomplete is quite "agressive" in VS 2005. I'm not the fastest typist, but no sooner does the letter "a" leave my fingertips but the word Application (or something similar) appears. I can't even use "conn" as an variable for Connections anymore, because the only way I can get autocomplete to leave it alone is to let it complete and backspace over the autocompleted class name. Going back to VS 2003 confirmed to me that it's new/different with VS 2005.

Does anyone notice this beside me? Does anyone know a way to turn down the "autocomplete promptness" setting? Autocomplete is normally one of my favorite VS features.

It turns out that the week before last, the SQL Server and ADO.NET teams (they may be part of the same team) made public the decision to merge the SqlClient data provider and in-proc SqlServer provider code into a single provider. Details are sketchy currently, but the reasoning behind this is that it's easier for programmers to use a single coding style. You'll be able to distinguish whether you're running in-server by either a bit switch or a connection string parameter. Even though I expressed my preferences to the teams, here's a couple of things I wanted to reiterate.

1. Keep SqlDefinition and SqlExecutionContext for the in-proc provider if at all possible. It's a nice way to optimize execution on the server, even if folks do tend to deduce (incorrectly) that it's related to prepared statements.

2. I've gotten to like SqlCommand's ExecuteSqlScalar method. This currently exists on the SqlServer provider but not on SqlClient. It should exist on both/merge. Here's why:

If I have an aggregate or scalar that can return NULL, it takes something like this code to use this with ExecuteScalar.

// this return NULL if no rows in table
SqlCommand cmd = new SqlCommand(
 "select max(id) from test", conn);

SqlInt32 i;
Object o = cmd.ExecuteScalar();
if (o.GetType() == typeof(System.DBNull))
  i = SqlInt32.Null;
else
  i = new SqlInt32((int)o);

Here's the code using ExecuteSqlScalar in SqlServer provider:

SqlCommand cmd = SqlContext.CreateCommand();
cmd.CommandText = "select min(id) from test";
SqlInt32 i = (SqlInt32)cmd.ExecuteSqlScalar();

Much cleaner, yes?

One of the most amazing things that happened at the class last week was Wednesday night, when Dan Sullivan and the SQL Server Service Broker team did “Night of the Service Broker”. Dan did a couple of back-to-back broker talks. Then, the entire SQL Service Broker team, lead by Gerald Hinson and Roger Wolter, participated in a panel discussion on how to effectively write apps that used broker and what made Service Broker unique.

Then Dan announced his new SQL Server Service Broker Developer's Spot. The site will host discussion forums, articles, tutorials, and also host cooperative development of some interesting service broker apps. It's open now, and they'll be sample applications (including the Service Broker client object model, courtesy of the team) up there shortly. So go on up and sign up now, if you're at all interested. Dan, me, Niels and others will see you there.

Syntactic changes are coming to the SQL Server in-proc provider. Compiling a stored procedure with the latest Dec CTP bits yielded the following message every time I used a method that started with "Get" from SqlContext:

warning CS0618: 'System.Data.SqlServer.SqlContext.GetPipe()' is obsolete: 'Will be removed soon'

Looking further with Reflector, it appears that SqlContext will now expose read-only static properties for Connection, Command, Pipe, etc, rather than using static methods named GetXYZ. So:

GetCommand -> replaced by CreateCommand
GetConnection -> Connection property
GetPipe -> Pipe property
GetTransaction -> Transaction Property
GetTriggerContext -> TriggerContext Property
GetWindowsIdentity -> WindowsIdentity Property

The old methods are still there (for now), but there may need to be a "mass rewrite" of beta code, samples, etc, soon. Just to let you know...

Another little thing I'd found had changed in Dec CTP. I'd reported a bug on this one and knew it was gonna be fixed eventually, but better sooner than later. I came up when Dan Sullivan suggested his "universal web service" based on SQL Server's XML data type and XML Schema Collections. Here's the prototype:

CREATE PROCEDURE some_ws (
 @somexmlinout   OUT xml(someothercoll))
AS
-- your code goes here
-- or your external name does

In previous versions, the HTTP Endpoint that defined this web service generated WSDL that, when uses with VS-proxy-generator, looked (coersed into objects) like this:

some_wssomexmlinout i = new some_wssomexmlinout();
some_wsResponsesomexmlinout j = new Responsesomexmlinout();

Object[] o = endpoint.some_ws(i, out j);

Representing the SQL OUTPUT variable as two variables not only made this more complex, but freaked out VB.NET proxy which doesn't really have a built-in concept of output-only parameters (though its possible to do with an attribute).

In Dec CTP, the "universal web service" builds proxies that look like this:

some_wsTypesomexmlinout i = new some_wsTypesomexmlinout();

Object[] o = endpoint.some_ws(ref i);

That's better. Even VB.NET likes it. Now if only I could provide a schema collection (or indicate in the endpoint definition) so that the WSDL would reflect (the only) two types in the namespace. Rather than it's (completely correct) definition of this as "any" from a collection of namesapces (those defined by the schema collection).

Well, unitl that happens, there is always custom WSDL generation...

Notice that this MUST have been a fix to HTTP Endpoint WSDL-generator, because my version of Visual Studio web service proxy generator (Oct CTP) was unchanged. Cool.

I answered a question on the newsgroups on how *exactly* inheritence works when you use it implement UDTs in SQL Server 2005. Also wanted to record the explanation here....

It doesn't work like you'd expect inheritence to when to use T-SQL, because SQL Server is blissfully unaware of the inheritence relationships (they're not recorded in the system views).

So if the class Tiger inherits from the class Cat. Passing an instance of Tiger to the T-SQL stored procedure FeedTheCat:
create procedure FeedTheCat (@thecat Cat) .... -- T-SQL here --
wouldn't work.

-- (Msg 206: operand type clash dbo.Tiger is incompatible with dbo.Cat)
declare @t tiger
execute feedthecat @t

-- or this (Msg 529: explicit conversion ... is not allowed)
declare @c cat
set @c = cast(@t as cat)
execute feedthecat @c

If the Cat class has a public instance method called FeedMe and a public field called pawcount (both are inherited by Tiger), this wouldn't work in T-SQL:

-- this wouldn't work
declare @t Tiger
print @t.FeedMe()
print @t.pawcount

-- this would
declare @c Cat
print @c.FeedMe()
print @c.pawcount

You could, however, access these fields/methods on Tiger from .NET code, As in:
// works fine
Tiger t = new Tiger();
int paws = t.pawcount;
// so does this
SqlString s = t.FeedMe();

Calling it in .NET code *through SqlCommand.ExecuteReader* (CommandText = "select sometiger.pawcount from zootab") wouldn't work.

OK, I couldn't help myself. The Dec 2004 CTP-specific readme had quite a number of things that are new. There are many improvements in SSIS, for example. The “real“ readme and the known issues file (sqlki.chm) still have July dates on them. But there are always a few little things I have queued up to try with a new build. Here's some that took 5 minutes after installing to test:

1. "Smiley face" XQuery comments still don't work. {-- comment --} still does.
2. INTERSECT and EXCEPT still not there. They are however, still in BOL.
3. FOR XML still does not work with UDTs. However, convert UDT to XML type DOES.

create table ctab (cnum complexnumber)
insert ctab values('1:1i')
insert ctab values('2:2i')

-- works! 2 rows returned with nicely formatted XML in them
select convert(xml, cnum) from ctab

-- fails, Msg 6865, FOR XML does not support CLR User Defined Types
select * from cnum for xml auto

I wonder why one works and not the other. Should use almost the same codepath for serializing UDT. Oh well. That's all for now. More later.

Now, back to our regularly scheduled technical content. About schemas, users, and owners.

Although Ed originally created the table, since Fred is the schema owner, Fred owns to table. Ed can get ownership of the table in either of two ways.

1. Someone with authority can alter the table's owner
2. Someone with authority can give Ed "take ownership" permission on the table

Until Ed has "take ownership" permission, he does not and cannot "own" the table he just created.

There are two ways to tell who owns a table. If you know who the schema owner is, "select * from sys.tables" produces a column named principal_id. If the principal_id is NULL, then the table is owned by the schema owner. If the principal_id is not NULL, the table has a specific owner. The other way is to use the OBJECTPROPERTY function and look for the property 'OwnerId'. This gives the exact owner, whether or not it's the schema owner.

This matters because, if you change the schema owner, the owner of the tables with NULL in prinipal_id changes. The owner of "specific-owner" tables does NOT change. So if the schema owner changes, say, to DBO, then DBO owns all the tables in the schema. BUT does not own Ed's table.

This whole thing is made possible because of the interesting meld that had to happen between a SQL Server-specific feature (ownership chains) and a new SQL2003-compliant feature (separation of users and schemas). Is this clear as a bell, now?

Just in case you don't believe it, code below (picks up where other code left off):

-- snip (when I left off, I was Ed)
-- ed cannot get ownership of table
-- this fails
alter authorization on object::fredstuff.edtab to ed
go

-- back to dbo
setuser
go

-- dbo can give the table to ed
-- alter authorization on object::fredstuff.edtab to ed
-- go

-- or dbo can give ed 'take ownership' permission
grant take ownership on fredstuff.edtab to ed
go

setuser 'ed'
go

-- now this works for ed, because he has 'take ownership'
alter authorization on object::fredstuff.edtab to ed
go

-- now ed can SELECT the table
select * from fredstuff.edtab
go

-- ed creates another table in the schema
create table fredstuff.table1 (id int)
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.database_principals
select * from sys.tables
go

-- owned by 'fred' (schema owner)
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed'
select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

setuser 'fred'
go

-- so can fred SELECT both tables
-- because fred is the schema owner
select * from fredstuff.edtab
select * from fredstuff.table1
go

setuser
go

alter authorization on schema::fredstuff to dbo
go

setuser 'fred'
go
-- no access for fred on this table
select * from fredstuff.edtab
-- access for fred on this table
select * from fredstuff.table1
go

setuser
go
setuser 'ed'
go
-- access for ed, he's still the owner
select * from fredstuff.edtab
-- never had access to this table
select * from fredstuff.table1
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.tables
go

select * from sys.database_principals
-- owned by 'dbo' (schema owner), this changed
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed', this did not change

select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

-- snip --

I'm probably about the  hundredth person to notice this and blog about it, but I'm not usually at home when these things happen. Thought I'd take the opportunity.

The SQL Server 2005 Community Technology Preview - Dec edition is available on MSDN subscriptions site

The Express Manager tool (free tool for management of SQL Express) is also available

It doesn't appear that you need an updated version on Visual Studio 2005 to work with this. You do need the Visual Studio 2005 *October* CTP, though. The Beta 2 version of Visual Studio 2005 won't do it...

Enjoy! 

The code for the answer is below. Greg Low is, of course, correct. Fred owns the table, but he owns it by virtue of being the schema owner. Now here's part 2. Do the following:

1. Alter the authorization on Ed's table so that it is owned by Ed.
   (Interesting aside, can Ed do this himself?)
2. Create another table (using either Fred or Ed) in the schema.
   Call it "fredstuff.table1" (my creativity for making sample names is legendary)
3. Alter the schema so that it's owned by another user (say, dbo)

Who owns each table now? How can you tell?

BTW, why does this matter? Because ownership chains go by object *owners*, not by schemas.

-- snip --
create database test
go

use test
go

-- make two users
-- fred has a default schema, ed does not
create user fred for login fred with default_schema = fredstuff
create user ed for login ed
go

-- create the schema for fred
create schema fredstuff authorization fred
go

-- fred and ed can create tables
-- ed can only create tables in fred's schema
grant create table to fred,ed
grant alter on schema::fredstuff to ed
go

setuser 'ed'
go

-- ed creates a table in fred's schema
-- who is the owner?
create table fredstuff.edtab (id int)
go

-- fred (schema owner) is the owner. not ed.
-- ed cannot even SELECT against the table he just created, this fails
select * from fredstuff.edtab
go
-- snip --

UI can't stay away from the separation and users and schemas feature. I want to make sure I have it cold, and following up on information I got from Girish Chandler's talk at Win-Dev, I did the following experiement. Posted in the form of a multi-part puzzle. Answer to the first part (with code) tomorrow.

1. You have two logins, Fred and Ed. Neither one has any special privileges
2. You create users for them in a database:
   Fred has a default schema of Fredstuff, which he owns
   Ed has no default schema
3. You grant both of them CREATE TABLE
   And Grant Ed ALTER priviledge on the Fredstuff schema (this lets him CREATE and ALTER objects in the schema).
4. Now Ed issues a CREATE TABLE statement to create a table in the Fredstuff schema

Who owns the table?

When you retreive a rowset that contains an XML data type column or use an XML data type column as parameter input, you have two choices. You can use the vanilla .NET type, System.String, or a special types from the System.Data.SqlTypes namespace called SqlXml. ADO.NET 2.0 handles conversion when you use strings (as long as they're valid XML, naturally). SqlXml has a method that produces an XmlReader and a constructor that takes XmlReader as input. Richer datatype.

Recently I wrote code that used an XML data type as an output parameter from a stored procedure. I specified the output parameter as SqlDbType.Xml and went to cast the result to the rich SqlXml type...invalid cast. Output parameters appear to always be returned as System.String or System.Data.SqlTypes.SqlString, NOT SqlXml. After the call, the SqlDbType is still reported as SqlDbType.Xml. *Input* parameters work fine as SqlXml, parameters with Direction InputOutput or Output are SqlString. Hmmm... Let's see if the upcoming Community Technology Preview, due out soon, helps this.

I returned home from Europe late Saturday night. With catching up on my sleep, email, questions, Thanksgiving (which Mary and I celebrated on Sunday this year), and everything else, I just realized its Tuesday morning and no blog entries. Time to blog again. I had two great classes, the caliber of the students was outstanding (you know who you are). One was given at Trivadis in Zurich, they were great hosts...thanks for having me over, folks. On the way back, I was actually upgraded to first class on transcontinental flight in airplaneland. You know, the one with the little pods and "bed chairs". Perhaps I'm too easily impressed.

A few weeks ago, I'd posted links to ADO.NET 2.0 articles I'd written for MSDN online. At that time, I mentioned there would be one more article in a series, but I'd save the subject as a "surprise". Well, the article just appeared: Tracing Data Access in ADO.NET 2.0.

Did you know there was built-in tracing in ADO.NET 2.0 and SQL Native Client? Were you surprised?

Also there's another nice whitepaper on when to (and when not to) use SQLCLR in SQL Server 2005. By the folks who brought you both SQLCLR and T-SQL enhancements in the upcoming new SQL Server release.

I was demonstrating SQLCLR appdomain usage (see previous post) to a class last week.Later on, I mentioned a different concept, that of dependent assemblies. This brought up the following question:

If user A owns assembly A and B owns assembly B, what happens if B contains a routine that calls A? Are two versions of assembly A available, owned by different owners? Or does this situation produce an error at CREATE ASSEMBLY time? Or at runtime?

The answer is that CREATE ASSEMBLY fails for assembly B, but with a fairly surprising error. The error is:

Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'Depends1' failed because assembly 'B' failed verification.  Run peverify on the assembly to determine the cause of failure.

Of course, running PEVERIFY on B works correctly, the error indicates that the dependent assembly 'A' could not be loaded into 'B's appdomain. This causes CREATE ASSEMBLY to fail.

It appears that you cannot work around this even by cataloging multiple versions of the assembly A, one version owned by A and the other owned by B. Even if there are multiple versions of the assembly in the database, SQLCLR only attempts to load the version that is cataloged so that the SQL Server name matches the name in the assembly manifest. As an example, even if I create assembly A owned by B and name it A1, attempting to catalog B fails with message 6218 above.

So if assembly A calls a method in assembly B, A and B must have the same owner.

A little-known behavior of SQLCLR (although we did mention it in our book) is that SQLCLR creates one appdomain per assembly owner, not one appdomain per database. One appdomain per database was the observed behavior in beta 1, although its important to remember that the algorithm for appdomain creation/destruction in SQLCLR is technically undocumented and subject to change.

This is NOT one appdomain per user, but one appdomain per assembly owner. This means that if user A owns assembly A and user B owns assembly B, A and B load in separate appdomains. Since remoting is not currently supported using SQLCLR, this makes allowing A and access B's classes and methods problematic. One workaround is to ensure that all assemblies that wish to share are owned by the same user, role, or application role.

BTW, one way to "see" the appdomain usage is to have a look at the SQL Server log. This contains messages for each appdomain creation and unloading.

This is actually posted a few days after I wrote it. I haven't done the “internet on the plane” service although I think it exists.

5:00AM Paris time, in the skies over ????

Right now, I'm in an alternate reality space that I call "AirplaneLand". I've racked up over 100,000 "airplane miles" this year, which is an indication of how much of my life is spent in this space. For instance, today Mary and I left the house at 5:45AM, she dropped me off in AirplaneLand at 6:15AM PST (I count the airport and other ancillary locations as part of this space) and when I arrive in Paris it will be 9:30AM the next day. That's over a day gone. Granted, some of that is due to timezone difference (I lose 9hrs going) but this still is a significant chunk of time, if you add it all up.

AirplaneLand is a life that consists of (too much) waiting in lines, hanging out at Red Carpet Club and gates, and, of course, the plane itself. The plane is the location where time expands and contracts. Oh yes, the Red Carpet Club. If you spend a LOT of time in AirplaneLand you are rewarded and treated better than everyone else. It's a amazingly intricate caste system; this evening at dinner, I got to request my dinner choice before almost everyone. I think they actually go by yearly milage. That's cool, I'm a "AirplaneLand resident" after all. Similar to the difference between "resident" and "non-resident" lines when checking through customs.

AirplaneLand has its own set of rules, especially lately. Today was my first look at the gate that they have to block people so pilots can go to the bathroom. 2 of use actually asked if it was electrified. With a straight face. My least favorite is the "use the bathroom of your own caste only" rule. The planes weren't configured for this, the bathrooms are in the wrong place. Everyone breaks this rule with impunity, but I seem to be one of the few who gets physically blocked by the airplane personnel. I was "less than thrilled" at the selective enforcement when this happened recently. It was empty, I was sitting immediately next to the "wrong" one and lots of folks had just tripped over me to get there (and they were the wrong caste too). Seemingly little things mean a lot in AirplaneLand.

Oh well, I told folks I'd write about my "second home" and that's it. In about 3 hours they wake us up and serve us "breakfast". Don't know where the day's gone...well, OK, I do know.

SQLNS is an acronym for SQL Server Notification Services. Originally introduced as a "free addition" to SQL Server 2000, it is an integrated part of SQL Server 2005. SQLNS is a framework for a specific kind of application, a publish-subscribe notification application.

We did a chapter on SQLNS 2005 in our "First look at SQL Server 2005 for Developers" book, but came up about 400 pages short of doing it justice for the true afficianado. Recently I'd heard about a few SQLNS-specific books being released, one by an acquaintance of mine from the SQLNS development team, Shyam Pather. Devs don't usually wrote books, and I just had to read this one. I just finished reading Shyam's amazing "Microsoft SQL Server 2000 Notification Services" from Sams press. It's just as good as I thought it would be.

I first met Shyam, Tete Mensa-Annan, and the other members of the SQLNS team when I spent a few days wallowing around in their knowledge of the product, in preparation for writing a DM course on the subject. They wanted to get developers and consultants up on the technology quickly. As far as I could determine, Shyam worked on the SQLNS engine itself, he and Phil Garrett and others spent a few hours with me a conference room, explaining the intriquicies of what they called "quantum theory", that is, how the SQLNS scheduling and execution engine service works. Tete spent a few hours explaining how SMS protocol and aggregators work in detail. I was amazed at the depth and bredth of their knowledge of the subjects. I could hardly write/type/think fast enough to keep up.

Back to the book. It's 600 pages of "SQLNS as a way of life", from beginning to end. It explores setup (without any gratuitous screen shots of setup, THANK YOU), configuration, programming instances and applications, writing the processing configuration files, the subscription management application. There are chapters about custom event providers, formatters, and delivery protocol; there is an example of each. But of course, where this book shines most (is shine most the correct construct? hmmm...) is in the description of internals, tuning, and troubleshooting. That's where it's invaluable. I still remember going over "quantum theory" and all its permutations when I read that chapter.

If you use SQLNS as a notification application framework, it's very easy to get a first instance working, but when your app gets popular and you need 10 more just like it immediately, working with SQLNS quickly becomes all-consuming, that's what I meant by "SQLNS as a way of life". If you're looking to "check out" SQLNS or see if its "right" for your application that's fine too, the preface even gets you quickly up to speed on the SQLNS lingo. I also remember Shyam writing to me a few times about the "its not a custom delivery channel, its a custom delivery protocol". In any case, there's *no way* you can be dissapointed with this book. I was so happy with it that I snagged a few copies to give away at the Guerilla SQL Server in Redmond. And Tete will be there to answer questions and help with SQLNS 2005 after the module/lab on it.

Every time I demonstrate using XQuery against a table that contains an SQL Server XML data type to a new class, a get about one out of four people with puzzled looks. And they're the ones who know XPath and XSLT. The part that seems weird to them is that a XQuery against what they see as "a collection of documents in a table" produces a one-row answer for each row in the table. You can tell they were expecting a single document (or fragment) answer. So,

SELECT xmlcol.query('/somequery') FROM xmltable

produces a rowset with a single column; there is one row in the "answer" for each row in the table. Eventually someone pipes up with: "well, I want just one answer". Can't you do that?

You can actually get just one answer in a few different ways. The easiest is to query the entire table using SELECT...FOR XML and do the query on the result:

DECLARE @x XML
SET @x = (SELECT xmlcol from xmltable FOR XML RAW, TYPE)
SELECT @x.query('/somequery')

The only problem you can run into with this is the maximum size of an SQL Server XML data type instance, which is ~2gig. You also have to take the "extra" elements that FOR XML generates into account in your final query. You can also do the original XQuery, then do a FOR XML to combine the results together.

But, you can even cut it finer than that. Using an XML.nodes query with CROSS APPLY or OUTER APPLY, you can generate one row in the answer for each node in the sequence that XML.nodes returns (including or ignoring empty-sequence rows, that's what CROSS vs OUTER apply does). These "intermediate rows" from XML.nodes actually contain "virtual documents" with the context node (starting point of the XQuery) possibly pointing somewhere other than the root. You then do XQuery against the virtual document on the left-hand side of the CROSS APPLY. Like this:

SELECT custid, tab.col.query('itemno') --get itemno subelement of lineitem
  FROM xmlinvoices
  CROSS APPLY (SELECT * FROM
    xmlinv.nodes('//lineitem')) as tab(col)

In this case, if you have a 10-row table, but the 10 XML documents in the table contain 42 lineitem elements, you get 42 rows in the answer. Cool 'eh? If you currently use OPENXML, learn XML.nodes, because this will eventually replace OPENXML in our toolbox. It's MUCH less memory-intensive than OPENXML.

Finally (someone asked this yesterday), if you DO want to start with 1-row/answer per row in table and just throw away the rows that contain empty sequence answer, a simple subquery will do:

SELECT * FROM
  (SELECT xmlcol.query('/somequery') AS col FROM xmltable) AS x
  WHERE col <> ''

There. Exactly the answer you want...whatever the problem. Have fun.

Looking for some cool SQLCLR and/or security-related features to show off. Before I hit the road again Friday, for a long stint in “airplane-land“ on way to Europe. Browsing through the SqlServer provider in Community Preview beta version of SQL Server 2005, I think I've found one. Came across SqlContext.GetWindowsIdentity.

With this call you can find out the Windows identity of a code's executor (remember procedures execute as caller by default) and also impersonate the caller (in SQLCLR) for the purpose of calling outside or inside the SQL Server process. By default, impersonation does not occur, this is a way to specifically make it happen. Only appears to work if assembly is cataloged as UNSAFE, though it would appear that it could be useful in EXTERNAL_ACCESS assemblies as well. Subject to SQL and Windows permissions.

It's even more interesting when used in conjunction with a SQL Server Login (which has no Windows credentials to speak of) and mapping Windows credentials to a SQL Server login with CREATE CREDENTIALS/ALTER LOGIN. The credential mapping appears to be a way to allow SQL logins to have an identity (all managed by the DBA and system administrators of course) in the underlying operating system.

[Comment heard from an XML afficianado] A: Uh...Yes, lots of them...

User-schema separation always leads to the recollection that "user-schema separation is the way things are defined in ANSI SQL 1999". Which brings up the subject of standards. Touting the ANSI SQL 1999 standard is passe now, because the ANSI SQL 2003 is out. And SQL 2003 "supercedes and obsoletes all previous ANSI SQL standards", the standard itself actually states this. Wow, does this make SQL 1992 twice-obsolete then?

Couple of points distinguish between the SQL and XML standards:
1. XML standards are usually hammered out (well version 1.0 is) before there are any/many official implementations to have "backward compatibility" issues. SQL standards began in earnest after the big players implemented RDBMS. ANSI SQL standards are much less rigidly followed.
2. SQL standards have a notion of "partial compliance", levels of compliance, and optional features much more than XML standards do. Both leave things open for "implementation dependent".
3. XML standards are freely posted on W3C website, though you must pay money to join the W3C. ANSI SQL, along with other ANSI standards are available at cost. Hmmm... what does this indicate? Dunno.

Standards commitees are fairly political (that's an understatement). There's a story that the original ANSI database standard committee was supposed to hammer out a standard for CODASYL databases, and RDBMS companies "stole the show". That's probably the tip of the iceburg.

For your edification and enjoyment, here are the official parts of the SQL 2003 specs:

— Part 1: Framework (SQL/Framework)
— Part 2: Foundation (SQL/Foundation)
— Part 3: Call-Level Interface (SQL/CLI)
— Part 4: Persistent Stored Modules (SQL/PSM)
— Part 9: Management of External Data (SQL/MED)
— Part 10: Object Language Bindings (SQL/OLB)
— Part 11: Information and Definition Schema (SQL/Schemata)
— Part 13: Routines and Types Using the Java™ Programming Language (SQL/JRT)
— Part 14: XML-Related Specifications (SQL/XML)

Part 14 is new. And is the basis for SQL Server 2005's XML data type. There are also parts (XA was part 6, I believe) that moved on to its a different spec series. And SQL/MM (multimedia, used to be part 8?) that has its own whole set of specs now. As does SQL Temporal (time series).

I wonder what happened to Part 5 - SQL Language Bindings? And I always wonder why parts 10 and 13 are couched entirely in terms of a programming language that is not itself an ANSI or even ECMA standard, but a "de facto" standard (rather than a "de jure" standard). Oh well...

The coolest SQL standard books are Jim Melton's two part series on SQL:1999. And SQL-99, Complete Really. Although they're both now officially obsolete (wonder if they'll be a SQL-2003 Complete Really?). Enjoy.

More on user-schema separation. In SQL 2000 and previous versions, granting someone CREATE TABLE privilege meant that they could create tables (no surprise there). The tables were "named after them" (e.g. bob.sometable) unless they were DBO. Because of user-schema separation in SQL Server 2005, that's no longer accurate.

I always explain this in terms of what you may not have the ability to do. And that, unless you have a default schema, your default schema is DBO, which you probably can't write to. There's a more polite/positive way of expressing this, courtesy of Dan. To be able to say...create tables, you need:
1. CREATE TABLE permission
2. A schema in which you are allowed to create objects. Or a schema that you or one of your roles own.

BTW, if this doesn't jibe with your experience, you're probably using the GUI (SSMS) or the legacy system stored proc sp_adduser, which creates a schema for you (for backward compatibility) rather than the new, cool (is security cool? yes, I think so), DDL statement CREATE USER. If you're using SSMS, to see what I mean:
1. Go to Security/logins in Object Explorer.
2. Choose to create a new login. Type in a login name/password
3. Click on database access
4. Permit access to a database by checking the Permit checkbox
5. Note that "default schema" and "user" get filled in with your userid
6. Click in the "default schema" cell. Oh.

NOTE that *default schema is a dropdown list*. You get to choose your default schema, or choose not to have one at all, in which case it's DBO.