I received email today that it was OK to talk about this. Our book “A First Look At SQL Server 2005 For Developers” was selected as “Best SQL Server Book” by the readers of SQL Server Magazine. You can read a short blurb about it here. And if you're a subscriber, you can read the whole article about the awards from their main page.

Congrats to Dan, Niels, and the folks at A/W who made this possible.

About a week or two ago, there was a LONG discussion on the SQLCLR beta newsgroup about the fact that the IsNull property that you use on CLR UDTs won't return TRUE or FALSE inside the server. It returns FALSE or NULL. Turns out that, although you use this property to *indicate* to the engine that your instance is NULL (database NULL, not null reference/value), the engine will optimize things by storing the fact that your instance is NULL. And so, a method called on a NULL instance yields NULL.

This was posted by the SQL CLR team here with a workaround if you *really* wanted this to work right, even inside the server. The workaround was to decorate your IsNull get method with:

[SqlMethod(OnNullCall=true)]

I tried this, it didn't work any better. But it WILL work if you use the correct field on the SqlMethod attribute. It's

[SqlMethod(InvokeIfReceiverIsNull=true)]

The difference is OnNullCall indicates whether a method will be called if any of its input parameters are NULL. This (OnNullCall=false) allows you to use non-SqlTypes as method parameters in your .NET code and not crash if someone passes in a NULL value. InvokeIfReceiverIsNull indicates whether the method will be called if the instance of the class itself is NULL. Obviously, not null class (you can't call a method on a null reference, for example), but database NULL.

This does work as advertised:

CREATE TABLE UDTTab (theUDT sometype);
go
INSERT UDTTab VALUES(NULL);
go
SELECT COUNT(*) FROM UDTTab where theUDT IS NULL
SELECT COUNT(*) FROM UDTTab where theUDT.IsNull = 1
go

both counts return 1.

I suppose its much easier "best practice" to remember is always use the SQL IS NULL in SQL statements. Because it's FASTER. They don't have to instanciate all those NULL UDT instances, just to confirm that IsNull is, indeed, true. And mark your “get” method for those who forget.

I've been working on a student question about using Impersonation inside of a stored procedure. This one's worth sharing.

You can do impersonation using the .NET SqlClient data provider using code roughly like this:

WindowsIdentity w = SqlContext.WindowsIdentity;
WindowsImpersonationContext c = w.Impersonate();
// do something here
c.Undo();

The rule is that in the "do something here" part, I'm allowed to do things like access the file system and these happen using the correct identity. But I'm NOT allowed to do data access. I'd always thought that "data access" meant using the classes in System.Data.SqlClient to access database data. But using the System.Data.SqlTypes.SqlXml class (which uses XmlReader) is also considered data access. So this code fails:

public static void LoadSomeXML(SqlXml thexml)
{
// impersonate
// do something here is:
   XmlDocument doc = new XmlDocument();
   doc.Load(thexml.Value);
// undo
}

interestingly, this code works:
public static void LoadSomeXML(SqlString thexml)
{
// impersonate
// do something here is:
   XmlDocument doc = new XmlDocument();
   doc.Load(thexml.Value);
// undo
}

because it doesn't use XmlReader to do the load. So if you pass in a SqlXml type parameter and use this class inside an impersonation context, it will fail. The error message says "Can't revert thread token in UDF/UDP..." so I wonder if this isn't related to some other threading issues reported using the impersonation context.

A good rule of thumb is to only do the minimum number of operations required while in the impersonation context and revert back (Undo) as soon as possible. In this case, all I really wanted to do was call doc.Save("somefile.xml") to save to the filesystem. If I move the declaration of XmlDocument and doc.Load() outside the impersonation context, doc.Save() works perfectly.

In answering a question about schemas, users, and objects (search on "schemas" to see the blog series I, II, III), I realized I never posted the portion about object resolution. Here goes.

When SQL Server resolves a one-part object name, the object resolution is slightly different if you're inside a stored procedure.

If batch or dynamic SQL:
1. Look in 'sys' schema for system objects
2. Look in user's deafult schema
3. Look in dbo schema

Note that if the user owner 100 schemas, SQL Server 2005 only looks in the default schema. If the user's  default schema isn't named after him, SQL Server 2005 never looks for name.object either.

If procedural code:
1. Look in 'sys' schema for system objects
2. Look in *procedure* schema
3. Look in dbo schema

Note that, in a stored procedure for example, SQL Server 2005 won't look in the user's default schema. Only the schema where the procedure lives.

Here's a code snippet that (hopefully) make this clearer:

create login ed with password='StrongPW!'
create user ed for login ed with default_schema = edstuff
go
-- default
create schema edstuff authorization ed
go
-- named after ed
create schema ed authorization ed
go
-- another schema for procs
create schema edprocs authorization ed
go
grant create table to ed
grant create procedure to ed
go

execute as user='ed'
create table edtable (id int, description varchar(100))
create table ed.edtable (id int, description varchar(100))
create table edprocs.edtable (id int, description varchar(100))
go
insert edtable values(1, 'im in edstuff')
insert ed.edtable values(2, 'im in ed')
insert edprocs.edtable values(3, 'im in edprocs')
go
-- procedure not in default schema, but in edprocs
create procedure edprocs.geted
as
select * from edtable
go

-- i'm in edstuff
select * from edtable
go

-- i'm in edprocs
execute edprocs.geted
go

drop table edprocs.edtable
-- invalid object name 'edtable'
execute edprocs.geted
go

drop table edstuff.edtable
-- invalid object name 'edtable'
select * from edtable
go
revert
go

I've spent the last few days talking with the data access folks at Microsoft. They're working on the next set of features and I've given input based on feedback I got from clients and students in the past two years. There may be some things I forgot about, however.

If you have an ADO.NET feature request, especially in the context of: “what problems are left unsolved in the data access space after .NET 2.0”, please feel free to write it here. Or if you're a former student or client, you can probably infer my new email address and write me mail.

If you're not particularly vocal about this issue now, prepare yourself for a featureset dictated by folks more vocal than yourself. It may be a hassle (and time-consuming) to provide feedback, it's even less useful to sit and grumble afterward.

I answered a question on the beta newsgroups last week about OSD and RSD (remember them?) by saying that those features had been pushed out into the future. The person then asked if the material in chapter 14 of our first look book had any practical value. I must admit that you can't cut and paste the examples and run them in the upcoming release. I don't know anything concrete about feature futures yet, so here's the long answer... from last week.

The features in chapter 13 and 14 were postponed, so they may appear in
future implementations. I used to tell folks those chapters are "very
futuristic". They may not appear line-for-line, class-for-class, the way
I coded them. But let's see where they are today without guessing at future.

ObjectSpaces was moved to the WinFS group. AFAIK (blogs and official notice)
it may not appear in WinFS as exactly the same implementation. In the last
WinFS public beta, OPath was/is the WinFS query language. Can't say what the
status of it all is today. There is no OSD or RSD in .NET 2.0.

Object-relational mapping as a concept has been around since there were
relational databases and object-oriented programming. Whether
codification/generalization of it into a product is a good idea is a subject
of *endless* debate, which I *don't* want to start again here. Some of the
products, past and present, have suffered from performance issues. But the
fact remains that if you are using relational data and object classes on the
client to consume/format/present that data, you are likely doing
object-relational transformation (even if it is very shallow) to some
extent.

System.Xml.Serialization is the preferred codification of XML-object mapping
in the .NET framework today. There is also the implementation in
System.Remoting.

The concept and implementation of a "query-intermediate language" mentioned
in chapter 13 was used in .NET 2.0 in XmlCompiledTransform class.
Client-side XQuery in .NET 2.0 was postponed because the spec is not
finished yet. Many implementations of XML consumers use a single library to
permit XPath/XSLT/XQuery in the same exe, probably don't use the
intermediate language concept, but use something coneptually similar. There
is no XSD/RSD mapping in .NET 2.0.

XML-relational mapping is in SQL Server 2005 in the guise of:
1. SELECT ... FOR XML
2. OpenXml and xml.nodes
3. SQLXML4 (which is part of SQL Server 2005)
4. SQLXML3 (which is still supported)
5. XML Web Services

There is an ISO/ANSI spec SQL2003 part 14, that codifies some/most/all of
these mapping concepts. In addition to SQL Server's implementation (in 2000
and 2005) other databases have similar but different ways of approaching
this problem.

So the class names, product/feature names, and implementation may change,
but the concepts and data models remain the same. As does the use of
multiple data models in the same programming project.

Hope this helps.

I'm known for my vivid imagination when making up test/exposition examples. I have a cat named Sam. So, once upon a time, I wrote:

CREATE CREDENTIAL myuser
 WITH IDENTITY = 'mydomain\myuser', SECRET = 'some56*Z'
GO

CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3'
GO

ALTER LOGIN sam WITH CREDENTIAL = myuser
GO

The DDL works. Now, I'd hoped to use this alternate credential so that Sam (a SQL Server login) could use the credential to use an external_access SQLCLR procedure that reads a file on the file system. This would require (since we have a nice NTFS file system with ACLs), that the SQLCLR procedure use the WindowsIdentity property on SqlPipe and do the impersonation. Works with Windows users, now Sam could do it too. I thought.

Just lately I found out that the alternate credential will not be useable with SQLCLR. WindowsIdentity will return null for Sam, regardless. This credential is useable with SQL Agent, something folks have always wanted for SQL Agent.

So no file system access for Sam, at least through SQLCLR and CREDENTIAL object. Unless the SQL Server service account has access to it and I don't do impersonation. He'll have to walk on the keyboard until he opens the file. As usual.

I've been wondering what happened to the QueryNotification dispatcher proc that's used by SqlDependency in ADO.NET (and in ASP.NET with SQL Server 2005). The one that I wrote about in one of my MSDN articles. Lately, although the dispatcher proc and assembly didn't show up in MSDB, the function kept working. I wondered why, how, what was happening.

This morning I installed the July CTP (which hasn't been reported to work with SQL Server 2005, so I didn't try) and found why. It's been "eased out".

There is now a static method called Start on SqlDependency (and a matching Stop method) that starts off ADO.NET's dependency listener. This creates a Service Broker queue and service (by default) and starts listening on it with a WAITFOR. So the functionality is no longer a passive listener (server pushes notification) but an active listener (strange as that sounds, means client listens and pulls notification). You pass a connection string into Start, but it looks like it will multiplex listeners on the same (1) connection.

Some nice repercussions of this (offhand, there may be more) are:
 No dependency of this feature on having SQLCLR enabled on server
 No possibility of DOS attacks on client
 No firewall issues since the listener uses one "normal" connection
 
More later...back to the revising/editing table for me.

Another fantastic book for your reading pleasure this month. “Customizing The Microsoft .NET Framework Common Language Runtime” by Steven Pratschner. In-depth coverage of .NET 2.0 hosting API. But why is this relevent?

Because most every bit of it is used by SQL Server 2005, as its hosting the .NET runtime. That's why. For example, as you read about “cocoon”, the host that will only load assemblies from zip files, ask: “who does this remind me of as a host”? Hmmm...

The book was written just recently, but as always there are minor implementation deltas. The one I noticed was that SQL Server 2005 does not create appdomains per-database/schema any more but per-database/assembly owner now. Small nit.

I did meet with Steven (he's an old friend from MTS days) when we were writing our “First Look” book, but apparently didn't take notes fast enough. His coverage is about 300 pages more than ours. And since then, he's moved on to Compact Framework, his CF blog is here.

Excellent book, dude...

They always get a bit of a jump on us WRT “current events” because of the timezone difference, especially if you live in the land of Pacific Time. I always come home on new year's evening and by before it's even dinner time, I've already missed new year in Sydney. We do get “Saturday Afternoon Footie“ here and I do watch it. On Friday night.

So....over the weekend Greg Low recorded a session with me for his podcast series at http://www.sqldownunder.com/. And I'd asked that he wait until Monday, until we'd announced the company move, to make it public. I woke up this morning and it was already there at SDU4FullShow.mp3 or SDU4FullShow.wma. We'll it's late afternoon, already. Hussled up the “Times they are a-changin'” blog entry really quickly and forgot to plug his/our podcast. Go git it.

I was a little distracted during the interview over UDT IsNull property and how its handled on the server. More on that later.

I really like the feature known as “All Permissions Grantable” in which any permission can be managed using the GRANT verb, because there are no “magic” logins or users anymore. The coolest thing is that all server roles are now defined in terms of the permissions they have; this chart is in the BOL. In it, sysadmin equates to CONTROL SERVER.

But I did notice when reading through BOL that many things are defined as “this can only be done by sysadmin”. But what if I'm not sysadmin and I do have CONTROL SERVER? Can I do those “sysadmin only“ things? Or the collolary: if I am granted CONTROL SERVER, do I become sysadmin (that is, show up in the system tables as sysadmin).

At least with the first three items I tried, I can perform the “sysadmin only” function but I'm not sysadmin. Good thing to remember when looking through an instance for high privilege users, to make sure some logins don't slip through the cracks. Here's example:

create login bob with password = 'StrongPW1'
go

grant control server to bob
go

-- login as bob or
execute as login='bob'
go
create database sample
go
-- this works for bob
alter database sample set trustworthy on
go

-- bob is not a member of sysadmin server role
-- BOL says he must be
sp_helpsrvrolemember 'sysadmin'
go

Just was looking through my reported problems last night to see what the resolutions might be. Looks like the two having to do with managed provider were fixed. But not yet, ie, the fix is not in June CTP according to the report.

Calling Connection.Dispose() and Command.Dispose() directly in-proc should now work, reported fixed.

6522 Error Anomaly when SQLCLR error occurs inside T-SQL TRY-CATCH reported fixed.

Look for things to start working better soon, I will be looking too. Thanks, folks.

Today I embark on a new venture, a new part of my career. I'm joining SQLskills as Director of Developer Skills. I've worked with SQL Server since version 4.21 off and on, taught classes about SQL Server at least part of the time since version 6.0, and, for the past three/four or so years, spent 95% or more of my time in this space. It's an exciting space, growing more exciting all the time, and that's the motivation behind the move. The folks I'll be working with here are another motivation.

I've heard of Kimberly Tripp "from afar" for quite a while. When I'd show up for Ascend gigs after she'd been to town before me, the folks that attended both classes would tell me stories about how "no one knows it better". All her speaker ratings at conferences we’ve both spoken at confirm this.  It will be great to be working with Kimberly at SQLskills.

We're planning some very interesting events for the future, so keep us on the radar. In the near future, I'll be working on these projects in addition to the next version of the "First Look" book (called SQL Server 2005 Developers Guide) and generally keeping busy.

Technical content is the point of this blog, as always. After almost a month of being "blog offline" (though the research never stopped), I've got some catching up to do on the blog. Stay tuned. If you found the blog here, pass it on.

Be sure to visit all the options under "Configuration" in the Admin Menu Bar above. There are 16 themes to choose from, and you can also create your own.

After writing a blog entry on "How DO you change your SQL Login Password" and complaining that "neither" SSMS nor SQLCMD allows you to change this" I was using this feature on the June CTP and, lo and behold, both of these utilities allow changing your password.

SQLCMD -? shows
-z new password
-Z new password and exit

And in SSMS, if you use a SQL login whose password has expired (or is designated "must change on first login") you get a nice, GUI-based “old password/new password/re-enter new password” prompt.

Don't know how I missed this before, sorry for complaining. If you use SQL Logins with login policies, code like this really should be part of your application. Perhaps a way to change password before it expires as well, like SQLCMD does. I'm still surprised that SSMS doesn't have this option on the login screen.

Sometime between when I left for Europe and when I started reading email that wasn't addressed "question from student" (yes, I do answer all those), my "Database Geek of the Week" interview got published. By a guy from Red Gate Software named Douglas Reilly.

The way I think this works is that someone suggests you for this honor. I don't know who suggested me, I wasn't told. Then Douglas writes some nice stuff about you and thinks up some questions he'd like to ask. Custom questions, too, from the way they sounded. Cool. He sends them to you and you answer in email. And he publishes questions and answers. Interesting concept.

On "my interview" page, there are links to articles (not mine) about "ADO.NET Data Access" and ".NET 2.0 transaction model". How relevent and coincidental.

Anyway, I'm honored...that someone would take the time to write questions and nice stuff about me and blog/ezine-style publish it. Thanks Douglas.

Wow. I was going through old blog entries looking for some unique insight (TM) I had, once upon a time, about SQL Server 2005 error handling in SQLCLR. Found a bunch of stuff, not sure how unique (or insightful) it was. But...

I realized I've been blogging over a year. First entry, July 1 2004 That's amazing, because I wasn't sure I understood the zen of blogging when I started. Not sure I do now either, but its been a whole year. I realize the entries have been slowing down as SQL Server 2005 has been coming closer to release. And as I start in earnest on the update of the "First Look" book. No technical content to this entry, just mindless reflection.

I just finished up our last gig for SQL Server 2005 Ascend last week in Paris (OK... in Les Ulis). It was my first class on the June CTP, and it seemed to go without incident. Students were enthusiastic and a good time appeared to be had by all. There are a two more "rescheduled for a later date" classes still to be done, but this is for all intents and purposes the end of Ascend phase 2 for us.

The only technical surprise of the week came when I added some rows to a table containing a UDT. After I did a "SELECT * FROM udttab" from SSMS, the rows are displayed in binary (back to the original behavior a la beta1), but when the UDT column contained a NULL value, I got the well-known "Assembly ... or one of its dependencies cannot be found". After making the assembly available I received, not the expected database NULL, but the binary value that corresponded to my UDT when it was NULL. Hmmm. It sounds like a few folks have been asking about this, and its been reported as a bug already. Interesting behavior. Using SQLCMD, the value NULL is displayed as you'd expect.

I just wanted to thank the folks from Ascend and thank the students as well. It's been a great time, I've enjoyed the traveling, the technical challange, and the nice reception that I received everywhere. Au revoir!

Now on to the release...keep on watching for more information.

In the April CTP of .NET 2.0, I chanced upon some changes to SqlNotificationRequest, using my favorite tool, .NET Reflector and my one my favorite investigative techniques, called “follow the error message”.

It turns out that two properties in SqlNotificationRequest the id and Service properties are about to be replaced. They still work but are marked “do not use, to be removed”. They'll but replaced by the UserData and Options properties. UserData appears to be a straight replacement for id, probably mandated by the .NET naming police. The naming police are possibly the same folks who replaced SqlContext.GetPipe and GetWindowsIdentity methods with the Pipe and WindowsIdentity properties. Making things consistent is nice, but its hard on folks who write things like demos, labs, slides, books, and articles.

SqlNotificationRequest.Options is more interesting. While the Service property only let you specify the Service Broker Service name, options gives you...you guessed it, more options. You can also specify which database the broker service lives in or even the Broker indentifier.

Suppose you wanted to listen for query notifications on a query on a table in the pubs database, using a service named “MyService” that also lives in the pubs database in your local SQL Server instance. Using the soon-obsolete Service and Id properties it would look like this:

SqlNotificationRequest not = new SqlNotificationRequest();
not.Id = Guid.NewGuid().ToString();
not.Service = "MyService";
not.Timeout = 0;
// now hook it up to the right SqlCommand

Using the new syntax would look like this:

SqlNotificationRequest not = new SqlNotificationRequest();
not.UserData = Guid.NewGuid().ToString();
not.Options = "service=MyService;local database=pubs";
not.Timeout = 0;
// now hook it up to the right SqlCommand

You can even use the Service Broker identifier GUID (look it up by “select name, service_broker_guid from sys.databases”) in the Options like this:

//NB: Service Broker service names are case sensistive!
//not.Options = "service=MyService;local database=pubs";
not.Options = "service=MyService;broker instance=CE086F11-C691-47F1-A8B6-1B7BD59EA6AE";

This property gives you the option of pointing at a service in a different database in the same instance, or even a different instance, subject to sercurity, of course. Happy query notifying. I gotta go fix a paper. And a book chapter. And a slide. And a lab. And... geez.

While at TechEd, I purchased the first computer book I've actually had to shell out hard cash for in quite a while, The Rational Guide to SQL Server 2005 Service Broker by Roger Wolter. Congratulations, Roger, you've got a winner here! Weighing in at just under 250 pages, this book is an explanation of Service Broker from its raison d'etre to the most intricate details. The first chapter says it's meant to compliment the info in the BOL, and the material fits right in while BOL leaves off.

I've had the pleasure of having Roger explain the Service Broker to me in person, quite a few times, but the book lays it all out as though you'd hung out with him for months. A great explanation of this extermely powerful but often misunderstood feature of SQL Server 2005. You might have to wait in line for this one, but it's worth waiting for. I got mine autographed too.

Are there any superlatives I've left out? Well if so, fill 'em in yourself.

I meant to blog from TechEd but with all the activity, I never got the time. The talk on when to use T-SQL and when to use SQLCLR drew an overflow crowd, and folks seemed to really like it. Thanks to all of you who attended. 

To attempt to summarize in one sentence, the “official” T-SQL vs SQLCLR positioning exists in the paper, “Using CLR Integration in SQL Server 2005” by Balaji Rathakrishnan, Christian Kleinerman, Brad Richards, Ramachandran Venkatesh, Vineet Rao. Since this is the list of folks who designed the product, it lays out the trade-offs and intended usage. Although the specifics have changed a bit since the paper was written (combined provider replacing SqlServer provider, new UDF coding style, etc) the paper's main points are just as valid today as they were when it was originally written, according to the authors.

Other than the talk, I spent a lot of time at the SQL Server cabana, hanging out with Kent Tegels and the folks on the SQL Server team, answering and asking questions. I especially enjoyed the “SQL Server 2005 BI Power Hour” talk, showing off use cases for SQL Server Integration Services, Reporting Services, Analysis Services, and Notification Services. My favorite demo showed how Reporting Services could be used to design a template for a Notification Services message by Shyam Pather. Very cool, Shyam. I hope we'll be seeing many more ways to integrate the various SQL Server 2005 product features in future.

I also spent quite a lot of time catching up with many old friends who I haven't seen in a long time. And also making some new friends. I don't do a lot of conferences as a rule, but this one was well worth it.

I commonly do a demo when teaching SQL Server 2005 where I write a SQLCLR UDF that's returns the string "Hello World". The define it, sans VS autodeploy, like this.

CREATE FUNCTION somefunc()
RETURNS NVARCHAR(4)
AS EXTERNAL NAME MyAssembly.MyClass.MyFunction

When invoked, it returns "Hell", silently truncating the string the CLR sent it. UNTIL Apr CTP. Now its returns "Truncation Exception". Surprise, surprise... After reporting this as a bug, I was told that "That's the way its supposed to work. To prevent silent data loss."

I agree. Except now its works differently than this T-SQL function:

CREATE FUNCTION somefuncT()
RETURNS NVARCHAR(4)
AS
BEGIN
  RETURN N'Hello World'
END

Correct again, it does work differently. The idea is the T-SQL one still silently truncates data, but the SQLCLR one does "the right thing". The T-SQL one still works the way it does for backward compatibility only. Maybe in the next release they'll work the same.

So what's your preference, backward compatibility or lack of silent data loss (and exceptions)? Just curious...

SQL Server 2005 will, by default on Windows Server 2003 systems, enforce password policies for SQL Server logins as well as Windows logins. Nice feature, but this means that your SQL Server login password can expire. So how do you change it? Well certainly the DBA can change it as (s)he always has, but you'd hate to bother your DBA every 42 days. Never mind what the DBA would think of that... And the user interface programs, SSMS and SQLCMD don't yet provide that feature. Neither does Visual Studio 2005 Server Explorer.

The functionality does exist in the supported database APIs. That is ADO.NET, OLE DB, and ODBC. With ADO.NET you have to be using 2.0, with OLE DB and ODBC the new SNAC providers are required. In ODBC, there is a new connection option SQL_COPT_SS_OLDPWD. In OLE DB there's an Old Password connection string parameter. In ADO.NET 2.0 SqlClient it's a static method on SqlConnection called (amazingly enough) ChangePassword. It takes to strings as input and here's how it works.

You change your connection code to use a loop, like while conn.ConnectionState == ConnectionState.Closed. Loop as many times as you like, most folks will probably loop twice. Bracket your calls to Open with a try-catch block. In the catch block, look for the following error codes:

18487 - Password Expired
18488 - Must change password on first login

If you get one of these call ChangePassword. You'd think that the parameters are "old password, new password". They are not. The first parameter must have enough information to connect to the server, including at minimum server name, your userid and your old password. The second parameter is just your new password. This changes your password, now change your connection string and Open again.

There are a couple of repercussions/refinements to this:

1. You obviously shouldn't even think about keeping password in the program, if you ever did this before. Check out my MSDN article for the built-in place to keep connection strings now.
2. This can only be used to change passwords on a SQL Server 2005 server. And only SQL Login passwords, naturally. It requires that the server and client be using the new network libraries.
3. There is no standard "New Password", "Old Password" GUI box. You need to make one yourself. Standard cavaets for passwords in GUIs apply.
4. There is no way to currently tell with standard SQL Server calls, how soon your password will expire. Think "Your password will expire in N days" message we all know and love.

So that's it. I have a "rough and ready" code example (that I wrote on a bet with Larry Chestnut at an Ascend gig a while ago) I'll probably clean up and post on my website eventually but this gives you the basic idea. And BTW, this isn't meant to push SQL Logins on anyone. If you can use only Windows logins in SQL Server (any release) and forgo SQL Logins entirely, PLEASE DO.