"Execute As" clause with a CLR based procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

targus - 13 Jan 2007 02:28 GMT

I need help with setting up security permissions. I created a CLR
based stored procedure to write a file on a network share. I have
tried Execute As but can only write a file locally on the database
server filesystem so far.
I believe the process is supposed to run as if the user had logged in
using the Execute As login id. Can someone tell me if I'm way off base
here?

--This works.
exec TableToFile 'c:\temp\test.txt', 'select * from book';
--this fails
exec TableToFile '\\myservershare\temp\test.txt', 'select * from book';

--This still fails
Execute As Login = 'mydomainlogin';
exec TableToFile '\\myservershare\temp\test.txt', 'select * from book';
Revert;

Is this the right way to use the Execute As feature?

Reply to this Message

Bob Beauchemin - 13 Jan 2007 07:37 GMT

It's not an outrageous idea, but attempting to retrieve a
SqlContext.WindowsIdentity object is an impersonated security context
returns null currently and is doc'd to do so. This occurs whether you use:
1. Execute as login = 'some_windows_login'
Execute some_clr_proc
or
2. Create (CLR) procedure with execute as
user='some_user_corresponding_to_windows_login'

If you create a CLR procedure with 'execute as dbo' (or as owner or as self
is they are dbo) this returns the SQL Server Service Account as the
WindowsIdentity and impersonating that account works. But that's the same
result as not impersonating at all (ie you access the external resource as
the Service Account).

Hope this helps,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb

>I need help with setting up security permissions. I created a CLR
> based stored procedure to write a file on a network share. I have
[quoted text clipped - 15 lines]
>
> Is this the right way to use the Execute As feature?

Reply to this Message