Bulk Insert permissions problem

Question

Bulk Insert permissions problem

Margaret Dullnig

Old Hand

Points: 373
More actions
November 14, 2006 at 7:15 am

#97894

I have a stored procedure that executes a bulk insert. The SP works fine for me (sysadmin), but when a user who has been put into the BulkAdmin server role executes it, the table is not populated, but no error is returned to the calling app (MS adp). I am qualifying the tablename with the owner name.
Any thoughts?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Stephen Pierce SSC Veteran Points: 251 More actions · Answer 1

Hi Margaret. You may also have to grant the db_ddladmin role to that user in the database you are doing the bulk insert.

Try that.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

Are you specifying the owner? If the user does not own the table or view which BULK INSERT is being executed against and the owner isn't specified, BULK INSERT will fail:

From Books Online:

Is the name of the table or view owner. owner is optional if the user performing the bulk copy operation owns the specified table or view. If owner is not specified and the user performing the bulk copy operation does not own the specified table or view, Microsoft® SQL Server™ returns an error message, and the bulk copy operation is canceled.

If the owner isn't specified... If the table is owned by dbo, a member of the sysadmin role is dbo, and therefore that would explain why it works for you and not for a normal user.

K. Brian Kelley
@kbriankelley

Margaret Dullnig Old Hand Points: 373 More actions · Answer 3

Thank you for your reply. Yes, I have read Books Online and am qualifying the table with the owner.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

Hrm. I just ran a test. Is the user the owner of the table? If not, I'm getting the following error when I run the test:

Server: Msg 8104, Level 16, State 2, Procedure insert_TestInsert, Line 3

The current user is not the database or object owner of table 'dbo.TestTable'. Cannot perform SET operation.

When the user is the owner of the table, everything works fine.

K. Brian Kelley
@kbriankelley

Margaret Dullnig Old Hand Points: 373 More actions · Answer 5

I was getting that same message when I was trying to pass in the table name and use dynamic code. I also got it because I had "set nocount off/on" statements.

For debugging, I removed the dynamic string and hard-coded the statement with the table name, and removed the set nocount. Now, when the user runs it from an adp, no error is return but no records are inserted. I can still run it with results.

This is my code (names were changed....), this same code is repeated with another table and source file directly following this code and then the error number is returned.

begin

delete dbo.tblTable

BULK INSERT dbo.tblTable

from '\\server\share\folder\File.txt'

with (FIELDTERMINATOR = '|',

FIRSTROW = 2

)

if @@error <> 0

set @ErrorSave = @@error

end

return @ErrorSave

Margaret Dullnig Old Hand Points: 373 More actions · Answer 6

Adding the user to the db_ddladmin role worked!

Thank you for your help.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

The only issue with that level of permissions is db_ddladmin has access to create and alter any objects within the database.

K. Brian Kelley
@kbriankelley

Margaret Dullnig Old Hand Points: 373 More actions · Answer 8

Thanks. We realize the risk and decided the two people who need to run this insert can have the permission.

If it was an SP for general use, I'd have to load the tables differently.

Thanks again, I appreciate your responses.