Introduction

The BULK

INSERT statement allows you to bulk copy data into SQL Server using the

functionality of the bcp utility with a Transact-SQL statement, rather than from

the command prompt. In SQL 2000 there is a fixed server role called “Bulk Insert

Administrators” which has rights to issue BULK INSERT, while in SQL 7.0 there is

no such role – only members of the Sysadmin role can execute BULK INSERT. This

creates a problem on systems where an application connects using a SQL login,

because usually you wouldn’t  want this login to be granted complete admin

access.

Solution

The

workaround is as follows:

Create a DTS

package which has the required Bulk Insert Task. Schedule the package. This will

create a job with a single step which runs DTSRUN in a cmdshell, and the

redundant schedule can be deleted. Next create a stored procedure to run the job

using : EXEC msdb.dbo.sp_start_job @job_name = 'JobName'.

Now we need

to enable permissions to execute this job.  In the Properties GUI of SQL Agent

(right-click on SQL Agent in Enterprise Manager) select the Job System tab and

uncheck the “Only users with SysAdmin privileges can execute CmdExec and

ActiveScripting job steps” checkbox. Usually the security context in which the

job is run is determined by the owner of the job. However, if the job is owned

by a login that is not a member of the Sysadmin server role (ie our case), then

the package is run under the context of the SQLAgentCmdExec account. So, as long

as this account is set up with the rights to access the source file, it all

works ok. Be sure to limit the rights of the SQLAgentCmdExec user, otherwise one

security loophole has been swapped for another.

Understandably this is not ideal, because now each user has access to (the

limited rights of SQLAgentCmdExec) to execute xp_cmdshell, but it may be

preferable to allowing the application login to have SysAdmin rights, and once

you upgrade to SQL 2000 this solution can be replaced by implementing the fixed

server role.

NB

It may seem that other simpler

methods can be used, but I found that if you try to run a stored procedure which

has the BULK INSERT command in it, you receive the following message “The

current user is not the database or object owner of table 'database.dbo.table'.

Cannot perform SET operation.” Making the user the DBO, simply changes the error

message to “You do not have the permission to issue the BULK INSERT statement”!

The same happens if you create a procedure which executes a job which in turn

executes the bulk insert statement.