none
Permission Denied to SA on Bulk insert

    Question

  • Dear All,


    I am experiencing strange problem (SQL 2005, build 9.0.1399), receiving following error:

    Msg 4834, Level 16, State 4, Line 1
    You do not have permission to use the bulk load statement.

    I am running the stored procedure SP1.  This stored procedure calls another one, SP2.  Stored procedure SP2 contains a dynamic SQL statement to insert data into the temporary table, created by SP1 and given to SP2 via parameter name.

    Something like:

    SET @szSQL = 'BULK INSERT ' + @TName + ' FROM ' + '''' + @FilePath + '''' +
      ' WITH ( ' +
      ' DATAFILETYPE = ''char'', ' +
          ' FIELDTERMINATOR = ' + '''' + @Terminator + ''', ' +
          ' ROWTERMINATOR = ''\n'' ' +
     ' ) '

    EXEC (@SQL)


    If I execute SP1 I receive an error

    Msg 4834, Level 16, State 4, Line 1
    You do not have permission to use the bulk load statement.

    But I can execute SP2 with no problem.

    All operations are done under SA login.

    Any thoughts/ideas?

    Thanks
    Tuesday, February 02, 2010 5:25 PM

Answers

All replies

  • Is the database you're using set to TRUSTWORTHY? That may be the issue... I think I ran into something similar once when nesting stored procedures and permissions.

    You may also want to review this connect item to see if it applies to you:
    https://connect.microsoft.com/SQLServer/feedback/details/125602/the-rights-for-administer-bulk-operations-are-checked-at-the-wrong-tmie

    -A
    • Proposed as answer by A.Lockwood Wednesday, February 03, 2010 12:21 AM
    • Marked as answer by Igor.K Wednesday, February 03, 2010 12:02 PM
    Wednesday, February 03, 2010 12:21 AM
  • Thanks.  You are right, the TRUSTWORTHY did solve the problem.  The SP1 was created with run as owner.  Removing that ore setting trustworthy helped.
    Wednesday, February 03, 2010 11:57 AM
  • I have the same problem.  Today I created a new database and tables.
    I have content for tables in delimited text files.

    The following command emits the Msg 4834 error, even after changing the database facet of Trustworthy = true.

    The login account is db_owner.

    USE MyDatabase

    BULK INSERT MyDatabase.dbo.MyUpdateTable
    FROM 'D:\FilePath\MyUpdateTable.data'
    WITH (
      DATAFILE = 'char', FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n'
    )

    Additional help would be appreciated!


    Good systems are supportable
    Saturday, February 27, 2010 1:46 AM
  • More Information: Server Permissions for User

    [x] public
    [x] serveradmin
    [ ] sysadmin

    With these permissions I get the Msg 4834 error when attempting BULK INSERT.

    [x] public
    [x] serveradmin
    [x] sysadmin

    With these permissions the Msg 4834 error appears to be resolved when attempting BULK INSERT.

    Good systems are supportable
    Monday, March 01, 2010 4:24 PM
  • More Testing - with SQL Server 2008

    In my case, the TRUSTWORTHY facet makes no difference.

    Without giving the user [ ] sysadmin privileges, the user should be granted [x] bulkadmin privileges.

    Server Permissions for User

    [x] bulkadmin
    [x] public
    [x] serveradmin (I have not examined this unchecked)
    [ ] sysadmin

    With these permissions the Msg 4834 error appears to be resolved when attempting BULK INSERT.

    This is also the case when the TRUSTWORTHY facet is set to "false".


    Good systems are supportable
    Monday, March 01, 2010 4:43 PM
  • In my case, the TRUSTWORTHY database facet makes no difference.


    Without giving the user [ ] sysadmin privileges, the user should be granted [x] bulkadmin privileges.

    Server Permissions for the User should include the "bulkadmin" privilege.

    [x] bulkadmin

    The TRUSTWORTHY facet (true/false) appears to make no difference
    on whether bulk insert is allowed.


    Good systems are supportable
    Tuesday, September 14, 2010 5:28 PM