none
Bulk insert and Execute As Permissions

    Question

  • Hello,

     

    Consider the following:

     

    create procedure jason_test

    as

    bulk insert SCORPIO_STAGE_BULK_DATAPDCC from 'd:\BulkTest\nonmech_stat_apd_clark_credit.dat' with (formatfile = 'd:BulkTest\DATAPDCC.fmt')

    go

     

    alter procedure jason_test_exec

    with execute as 'bulk_insert_test_jcb'

    as

    bulk insert SCORPIO_STAGE_BULK_DATAPDCC from 'd:\BulkTest\nonmech_stat_apd_clark_credit.dat' with (formatfile = 'd:BulkTest\DATAPDCC.fmt')

    go

     

     

    Then, log into SQL Server via management stuido as the SQL user "bulk_insert_test_jcb"  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 you help me with this?  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.

     

    Thanks!

    Jason

     

     

     

    Friday, May 09, 2008 4:07 PM

Answers

All replies

  • The WITH EXECUTE AS runs the procedure are the user specified.  The proc first checks to assure that the user calling has appropriate permissions to execute the stored procedure.  If the user has permission then the proc will run under the security context of the user specified in the WITH EXECUTE as staement.  The user bulk_insert_test_jcb does not have bulk insert permissions.  This was added in SQL 2005 to over come broken security chains and is a great tool.  To better understand you can run this:

     

     

    Code Snippet

    USE AdventureWorks;

    GO

    --Create two temporary principals

    CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';

    CREATE LOGIN login2 WITH PASSWORD = 'Uor80$23b';

    GO

    CREATE USER user1 FOR LOGIN login1;

    CREATE USER user2 FOR LOGIN login2;

    GO

    --Give IMPERSONATE permissions on user2 to user1

    --so that user1 can successfully set the execution context to user2.

    GRANT IMPERSONATE ON USER:: user2 TO user1;

    GO

    --Display current execution context.

    SELECT SUSER_NAME(), USER_NAME();

    -- Set the execution context to login1.

    EXECUTE AS LOGIN = 'login1';

    --Verify the execution context is now login1.

    SELECT SUSER_NAME(), USER_NAME();

    --Login1 sets the execution context to login2.

    EXECUTE AS USER = 'user2';

    --Display current execution context.

    SELECT SUSER_NAME(), USER_NAME();

    -- The execution context stack now has three principals: the originating caller, login1 and login2.

    --The following REVERT statements will reset the execution context to the previous context.

    REVERT;

    --Display current execution context.

    SELECT SUSER_NAME(), USER_NAME();

    REVERT;

    --Display current execution context.

    SELECT SUSER_NAME(), USER_NAME();

    --Remove temporary principals.

    DROP LOGIN login1;

    DROP LOGIN login2;

    DROP USER user1;

    DROP USER user2;

    GO

     

     

    Friday, May 09, 2008 4:24 PM
  • Yes, the user bulk_insert_test_jcb does have bulk insert permissions.  If he did not, then the first execution of "jason_test" would fail.  the user can also run the bulk insert directly from the query window and it works fine.

     

    Friday, May 09, 2008 4:28 PM
  •  

    Another forum member found this and this explains what is happening:

     

    http://support.microsoft.com/kb/913422

     

    thanks,

    Jason

     

    Friday, May 09, 2008 4:37 PM