SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Allowing BULK INSERT for non-SysAdmin Users in SQL 7.0

By Paul Ibison,



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.




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.




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.

Total article views: 5853 | Views in the last 30 days: 7
Related Articles

Rights to execute stored procedure

Rights to execute stored procedure


BCP Trigger Hangs up Insert Statement

Creating BCP trigger on Insert Hangs on Insert Statement Execution


Execute msmdarch without Windows Administrator Right

Execute msmdarch without Windows Administrator Right


[Execute SQL Task] Error

[Execute SQL Task] Error: Executing the query "INSERT INTO


Create Sql Job

want to create sql job which execute code per minute

sql server 7