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.