Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Allowing BULK INSERT for non-SysAdmin Users in SQL 7.0

By Paul Ibison,

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.

Total article views: 5843 | Views in the last 30 days: 8
 
Related Articles
FORUM

Rights to execute stored procedure

Rights to execute stored procedure

FORUM

BCP Trigger Hangs up Insert Statement

Creating BCP trigger on Insert Hangs on Insert Statement Execution

FORUM

Execute msmdarch without Windows Administrator Right

Execute msmdarch without Windows Administrator Right

FORUM

Create Sql Job

want to create sql job which execute code per minute

FORUM

[Execute SQL Task] Error

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

Tags
administration    
security    
sql server 7    
 
Contribute

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones