There's a cool feature in SQL Server 2005 Express that you might already be using without realising it. They're called "User Instances" and they're really cool because they allow you to attach to database files without the need for being an administrator on the local machine.
Basically, what happens is, if you specify "User Instance" in your connection string, SQL Server will spawn a new sqlservr.exe process running under your own user account. That instance of sqlservr.exe can attach to any .mdf file that your user account has write-access to and you then have "dba" access to that database file (so you can create tables, stored procedures, load SQLCLR, etc, etc). All without you being an administrator.
It works great for me when doing my unit testing, because I can have my unit test project set up a "dummy" database in %TEMP% which my tests do all of their work in, and then I just throw it away at the end of the session. Once all the connections have gone away, SQL Server will automatically close the file, so all I have to do is SqlConnection.ClearAllPools(); File.Delete(pathToMdfFile);.
There's just one minor drawback. If something goes wrong in my unit test, sometimes I'd like to connect to the database using sqlcmd.exe to execute some ad-hoc queries or whatever. The problem is that User Instances are only available "out-of-the box" to .NET-based SqlClient programs. Now, I guess I could write a custom .NET-based query processor, but that seems like a lot of work just for a coupe of ad-hoc queries.
Besides, it turns out you can connect to User Instance with SQLCMD using the "np:" server name format. That is, if you know the named pipe that the sqlservr.exe process is using, you can specify that in the -S SQLCMD command-line to connect directly to it. The hard part is actually finding out the named pipe name. Basically, you need to execute the following command to get the value from the "main" SQL Server instance:
C:\> SQLCMD -S .\SQLEXPRESS -E
1> SELECT instance_pipe_name FROM sys.dm_os_child_instances
2> GO
instance_pipe_name
-----------------------------------------------------------------------------
\\.\pipe\47820298-525E-44\tsql\query
(1 rows affected)
1> quit
C:\>
The biggest problem, however, is that you need dba access to be able to execute that query, which pretty much defeats the whole purpose!
Anyway, assuming you've got the pipe name, you can now connect to the User Instance by executing the following command:
C:\> SQLCMD -S np:\\.\pipe\47820298-525E-44\tsql\query -E
1> USE [C:\path\to\db.mdf]
2> go
Changed database context to 'C:\PATH\TO\DB.MDF'.
1>
And there you go! If only it was a bit simpler (and didn't require dba access to the main SQL Server instance) to do...