May 9, 2008 at 10:16 am
Hello,
Consider the following:
create procedure jason_test
as
bulk insert Table1 from 'd:\BulkTest\Source.dat' with (formatfile = 'd:BulkTest\Format.fmt')
go
create procedure jason_test_exec
with execute as 'bulk_insert_test_user'
as
bulk insert Table1 from 'd:\BulkTest\Source.dat' with (formatfile = 'd:BulkTest\Format.fmt')
go
(the only difference is the execute as clause)
Then, log into SQL Server via management stuido as the SQL user "bulk_insert_test_user" this user has server-level bulk admin rights and execute rights on both of these stored procs:
exec jason_test
This works
exec jason_test_exec
gives:
Msg 4834, Level 16, State 1, Procedure jason_test_exec, Line 4
You do not have permission to use the bulk load statement.
Can anyone help? Why is the user prevented from running this bulk insert inside the stored proc with "execute as" ? The profiler trace from both of these stored procs have identical results for the SP: StmtStarting event.
Note that this is SQL 2005, SP2 (version 9.00.3054). No culmulative hotfixes applied. 64 bit Enterprise Edition. Our exact problem is more involved than this, but this isolates the concern.
Thanks!
Jason
May 9, 2008 at 10:26 am
My guess is that certain server level permissions cannot be trasnferred this way. Perhaps this explains it?
http://support.microsoft.com/kb/913422
Is this a user and not a login?
May 9, 2008 at 10:33 am
Hi Steve,
the "bulk_insert_test_user" is a SQL Login and a member of the BulkAdmin server role. It is a user in the database in question with execute rights to both of the test stored procedures.
With all that said, the kb article you reference nailed it. I set trustworthy on for a quick test and now both stored procs run.
thanks!!
Jason
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply