After writing about a lot of new security features that were added since we published our "First Look at SQL Server 2005 book" its nice to report on one that we had in there (at beta2), but never appeared in the product. Until now.

In the September CTP version on SQL Server 2005 (I think its probably the last CTP), you need special permissions to CREATE an ASSEMBLY with UNSAFE permission set. You must have either one of the following:

1. DBO has UNSAFE ASSEMBLY permission and database has TRUSTWORTHY property on.
or
2. ASSEMBLY is signed with an asymmetric key or cert that has a LOGIN with UNSAFE ASSEMBLY permission.

We'd written about the second choice. Here's one of the combinations that works:

1. Create a strong named key in c:\temp\assm.snk
2. Sign the assembly unsafe1.dll with this strong named key
3. Make a SQL Server LOGIN for the key.
4. Give LOGIN the appropriate permissions
5. Catalog the unsafe assembly

In code, it looks like this:

-- master key in master database
USE master
go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword1'
go

-- keyfile generated by VS or .NET command line utilities
CREATE ASYMMETRIC KEY assm FROM FILE='c:\temp\assm.snk'
go

CREATE LOGIN snk FROM ASYMMETRIC KEY assm
go

GRANT UNSAFE ASSEMBLY TO snk
GO

USE somedb
GO

CREATE ASSEMBLY unsafeassemblyex FROM 'c:\temp\unsafe1.dll'
  WITH permission_set = unsafe
GO

That's only one variation of it. You can also use the key stored in the assembly (CREATE ASYMMETRIC KEY FROM EXECUTABLE FILE=...) or an assembly already cataloged inside the database (CREATE ASYMMETRIC KEY FROM ASSEMBLY...). You can do the same thing with certificates.

So one of the SQLCLR security features we wrote about over a year ago has come to pass.