I've been lying pretty low lately, doing research on some topics in a slightly different space (but at least obliquely SQL Server 2005 related). Came across a list of interesting student comments today, and I was back trying out (picking at?) one of my favorite spare-time topics to answer a question about. SQL Server 2005 metadata. The question was whether or not the INFORMATION_SCHEMA views were security-sensitive, and...of course they are. There are now (at least) 4 different ways to list the user tables in a database (all subject to security) and they are:

select * from sysobjects where type = 'U'
select * from sys.objects where type = 'U'
select * from sys.tables -- I always wanted a systables in earlier versions
select * from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE_TABLE'

The Dec CTP closed the last loophole; using system procedures like sp_help is now permission sensitive.

But what if you WANT a user to be able to list all of the tables in the database? The “smallest“ permission you can give a user is VIEW DEFINITION privilege on a specific object. As an example, if I had a user named 'fred' that I wanted to 'see' the authors table, I would give:

grant view definition on object::authors to 'fred'

A specific resource is called a securable. Securables exist in a hierarchy. So although there's no straightforward way to give VIEW DEFINITION on all of the tables (that I can see) in a specific schema or database in a single statement, I could give a user access to all of the objects (Table, View, Function, Procedure, Queue, Type, Rule, Default, Synonym, Aggregate, XML Schema Collection) in a particular schema with this one statement.

grant view definition on schema::dbo to 'fred' -- all in dbo schema
grant view definition on database::dbo to 'fred' -- all in the database

Note the VIEW DEFINITION is the “smallest“ permission; it gives a user permission to see that an object exists without being able to see the data. For fred's case, “select * from authors“ would still fail. Giving SELECT also gives view definition privilege, so if you can SELECT against an object, it also shows up in your metadata list. Makes perfect sense. You can tell I'm liking the new metadata views...and the new “all permissions grantable“ concept. Wait until the first user calls up and asks “where did all of the tables in my dropdown list go?“.

HTTP Endpoints in SQL Server 2005 are normally a way to support SQL Server clients that speak the SOAP protocol. It turns out that, although the HTTP endpoint requires a SOAP request, the response need not necessarily be SOAP or even XML. You can return something other than the vanilla document-literal SOAP format; the way to accomplish this is to use FORMAT=NONE in your WEBMETHOD declaration.

Usually HTTP endpoints return an XML complex type, SqlResultStream; you get this behavior with FORMAT=ALL_RESULTS, the default. FORMAT=ROWSETS_ONLY produces only SqlRowSet complex types, this shows up in .NET clients as a DataSet. FORMAT=NONE is the alternative. When you specify NONE, SQL Server doesn't even bother to wrap the result in a SOAP packet. You can use this for sending *anything* back to the client. Although your response can be a nicely formatted SOAP packet, it can also be any XML, more complex SOAP (e.g., additional SOAP Headers). The response doesn't even have to be XML at all, anything can be streamed back to the client, although this has a potential for surprising the receiver.

You use FORMAT=NONE with a stored procedure that has no input or output parameters. It must return a resultset with a single "column" with the special name "XML_F52E2B61-18A1-11d1-B105-00805F49916B". This name is actually an indicator to the underlying protocols that the result should be streamed back to the client, rather than formatted into columns and rows. You might be wondering where you've seen this column name before; if you use SELECT...FOR XML queries, this is the “column” that these queries return. You can also return raw SELECT...FOR XML output using FORMAT=NONE.

When I first heard of this option, the use that immediately came to mind was support of advanced web service procotols. Although I can *output* non-vanilla SOAP using FORMAT=NONE, there doesn't currently seem to be a way for a "webmethod" stored procedure to get ahold of the raw *input* message to an HTTP Endpoint. So, right now, I can't send complex input and have the stored procedure respond to it. Hmm...

I'm still assimilating little tidbits of information about the Nov-Dec CTP build. Put this one in your pubs database in the CTP:

create procedure deletejob (@id int)
as
begin try
 delete jobs where job_id = @id
end try
begin catch
 -- you knew about these
 print error_number()
 print error_message()
 print error_severity()
 print error_state()
 -- these are undoc'd but work in the CTP
 print error_line()           -- line number
 print error_procedure()  -- procedure name
end catch
go

execute deletejob 2
go

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.