This article will provide an overview of how to use SQL Server Bulk Operations, such as BULK INSERT and OPENROWSET(BULK…).
Shared SQL Servers
For shared SQL servers, the BULK INSERT command is not allowed. This operation requires that the import file resides on the SQL server itself (not the web server), which is difficult to accommodate in a shared hosting environment.
If you wish to use one of the OPENROWSET bulk operations to pull information from a remote datasource, you can request the ADMINISTER BULK OPERATIONS permissions from our support team by contacting support and referencing this article. You will need to include the SQL login that will be used for the bulk operations so that we can add that login to the BULK ADMIN role. This will grant the ADMINISTER BULK OPERATIONS permission to that login.
Enabling for VPS Servers
For VPS servers, there are a few steps that will need to be taken in order to allow BULK commands:
-
You must add the ADMINISTER BULK OPERATIONS permission to the SQL login that will be performing the action. However, if you are performing the operation manually via SQL Server Management Studio while logged in as a sysadmin user, you will already have this permission since the sysadmin role includes all permissions.
Using SQL: Below is the command to add this permission (replace [server_login] with the actual login name):
ALTER SERVER ROLE bulkadmin ADD MEMBER [server_login]Using GUI: Edit the SQL login within SQL Server Management Studio and add the user to the BULK ADMIN server role.
-
(BULK INSERT only) The SQL service account must have permissions to read the file, which involves a couple of steps:
-
Determine the SQL service account by opening the Windows Services dialog (Run -> services.msc):
-
Add the SQL service account to the filesystem permissions/ACLs for the folder containing the bulk data file you are wanting to import with at least Read permissions:
If the Users group already has read permissions (as shown above) or the SQL service account already has read permissions, then nothing needs to be done. The Users group includes all users on the machine, so this will also include the SQL service account unless it is a domain account.
-
Bulk Operation Usage
For usage of the actual bulk commands, we recommend referring to Microsoft’s documentation. They provide a thorough explanation of each bulk command along with examples: