There is some literature out there that says it is possible to impersonate the current user’s logon for external access (file system, registry, etc.) in SQL/CLR sprocs, but not much in the way of sample code. As I have crossed this bridge, I thought I should share the knowledge. This now works with assemblies catalogued as EXTERNAL_ACCESS. I think in previous betas, it would only work on UNSAFE assemblies.
// Use current user's context to access external resource - unless
// logged in using SQL authentication where WindowsIdentity will
where WindowsIdentity will // be null and will use SQL Server service account
WindowsImpersonationContext newContext = null;
WindowsIdentity newIdentity = SqlContext.WindowsIdentity;
try
{
if (newIdentity != null) newContext = newIdentity.Impersonate();
// do external access here
}
catch
{
// ...
}
finally
{
if (newContext != null) newContext.Undo();
}
Note
EXECUTE AS LOGIN = 'login1' allows impersonation of a login from TSQL.
The WITH EXECUTE AS clause of stored procedure declarations allows the execution context to revert to a context other than the default of CALLER in the event that the object-ownership chain is broken (e.g. dynamic SQL). For example, it could revert to the context of the stored procedure owner.
These options have no effect on impersonation outside SQL Server.