Bulk Insert Via Stored Proc

Hello,

Please consider the following:


CREATE procedure [dbo].[jason_test]

with execute as 'bulk_insert_test_jcb'

as

exec('bulk insert SCORPIO_STAGE_BULK_DATAPDCC from ''\shodbs29CDRData
onmech_stat_apd_clark_credit.dat'' with (formatfile = ''\dixdbs01ScorpioBulkDATAPDCC.fmt'')')



This is a stored proc with execute as a SQL user. It runs one bulk insert. The user bulk_insert_test_jcb does have BulkAdmin rights and if the user is logged in directly to the server, this works fine. If a SQL user is logged in and runs it (a user other than bulk_insert_test_jcb), this also works

However, if I run this as a windows user logged into the server


alter database stage_scorpio_bulk_jcb set trustworthy off

exec jason_test

--Msg 4834, Level 16, State 4, Procedure jason_test, Line 4

--You do not have permission to use the bulk load statement.


I expect this because the server-level permissions (bulk) are stripped off unless the database is trustworthy, so...


alter database stage_scorpio_bulk_jcb set trustworthy on

exec jason_test

--Msg 4861, Level 16, State 1, Procedure jason_test, Line 4

--Cannot bulk load because the file "\shodbs29CDRData
onmech_stat_apd_clark_credit.dat" could not be opened. Operating system error code 5(Access is denied.).


Why does this happen? I thought that, since I'm executing as a SQL user, SQL Server would authenticate over to the server with the datafiles as the service account, but I see the following in the log at SHODBS29


--User Logoff:

-- User Name: ANONYMOUS LOGON

-- Domain: NT AUTHORITY

-- Logon ID: (0x0,0x4C99BD2F)

-- Logon Type: 3

--

--

--For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


Any ideas? It seems as if it is still trying to used windows authentication even though the stored proc is supposed to execute as a SQL user.

Someone in another forum said that ownership chaining not being allowed for bulk operations was the problem, but I don't think so, since if I put an "execute as user='bulk_insert_test_jcb'" into the exec string, it still fails with the same issue.


Thanks!
Jason