How to enable bulk permission in SQL Server

Try GRANT ADMINISTER BULK OPERATIONS TO [server_login]. It is a server level permission, not a database level. This has fixed a similar issue for me in that past (using OPENROWSET I believe).


Try this:

USE master;

GO;
 
GRANT ADMINISTER BULK OPERATIONS TO shira;

Note that the accepted answer or either of these two solutions work for Windows only.

GRANT ADMINISTER BULK OPERATIONS TO [login_name];
-- OR
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [login_name];

If you run any of them on SQL Server based on a linux machine, you will get these errors:

Msg 16202, Level 15, State 1, Line 1
Keyword or statement option 'bulkadmin' is not supported on the 'Linux' platform.
Msg 16202, Level 15, State 3, Line 1
Keyword or statement option 'ADMINISTER BULK OPERATIONS' is not supported on the 'Linux' platform.

Check the docs.

Requires INSERT and ADMINISTER BULK OPERATIONS permissions. In Azure SQL Database, INSERT and ADMINISTER DATABASE BULK OPERATIONS permissions are required. ADMINISTER BULK OPERATIONS permissions or the bulkadmin role is not supported for SQL Server on Linux. Only the sysadmin can perform bulk inserts for SQL Server on Linux.

Solution for Linux

ALTER SERVER ROLE [sysadmin] ADD MEMBER [login_name];

SQL Server may also return this error if the service account does not have permission to read the file being imported. Ensure that the service account has read access to the file location. For example:

icacls D:\ImportFiles /Grant "NT Service\MSSQLServer":(OI)(CI)R