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

Question

Using SQL Server 2016, I am trying to configure a user other than 'SA' to import a file. The code I am executing is as follows:

EXECUTE AS USER = 'DataImports';

SELECT  CURRENT_USER;

EXEC xp_cmdshell 'TYPE myFileNameHere.txt'

BULK INSERT DataImports.staging_AddressBook
FROM 'myFileNameHere.txt'
WITH (DATAFILETYPE = 'char'
,     FIRSTROW = 2
,     FIELDTERMINATOR = ' '
,     ROWTERMINATOR = '\n');

The error that I get is:

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

I have validated the following:

• I do have access to the file as the user required - The cmdshell TYPE returns the rows expected. I do not appear to have a file access issue.
• I have INSERT permission on the database in general.

I tested by using:

SELECT      
    [DatabaseUserName] = princ.[name],
    [PermissionType]   = perm.[permission_name],
    [PermissionState]  = perm.[state_desc]
FROM      
    sys.database_principals  princ
LEFT JOIN 
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
WHERE      
    princ.[name] = 'DataImports';`

• I do have the bulk admin role

  SELECT
      r.name AS [RoleName],
      m.name AS [MemberName],
      CASE 
         WHEN m.name IS NOT NULL THEN 1 ELSE 0 
      END AS IsMember
  FROM      
      sys.server_principals r
  LEFT JOIN 
      sys.server_role_members rm ON (r.principal_id = rm.role_principal_id)
  LEFT JOIN 
      sys.server_principals m ON (rm.member_principal_id = m.principal_id)
  WHERE      
      r.type = 'R' AND m.name = 'Dataimports';
  

  

I have even configured the user to be a sys-admin (not part of the long term plan) but I'm still getting the error.

These are the main points that have been highlighted in the other SO tickets and general searches I have performed. I can import the table as SA but not as DataImports despite what appears to be correct configuration.

This is part of a job that is being run and currently we are having to give SA access just to read a file. Security wise this is less than ideal but I cannot work out what is missing.

Any suggestions of what else to check would be gratefully received - all the basics seem to be in place.

Answer 1

Any suggestions of what else to check would be gratefully received - all the basics seem to be in place.

Few things:

GRANT ADMINISTER BULK OPERATIONS TO Dataimports

If the destination table contains triggers or checks constraints

GRANT ALTER ON TABLE DataImports.staging_AddressBook TO Dataimports

And

ALTER DATABASE [yourDB] SET TRUSTWORTHY ON;

Because of:

For security considerations, the server-scoped permissions are stripped down when you impersonate a database user unless the system administrator has explicitly set SQL Server to trust the impersonated context at the server-scope. In this case, a login with the control server server-scoped permission has no permissions to access any particular database. Therefore, the trigger module that is executed as this login cannot run.