Allowing BULK INSERT for non-SysAdmin Users in SQL 7.0

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.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.