January 19, 2010 at 2:16 am
One user needs to do bulk insertion into database from excel file. I granted him BULKADMIN role as well as he is also owner of db (db_owner). But while executing below statement, he is getting the error:-
Cmd.CommandText="BULK INSERT dbo.tempLDRsupv924 FROM '\\chr-ba- dev\filename.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '');"
Set objRS = cmd.execute
Error it produced:
Microsoft OLE DB Provider for SQL Server error '80040e14'
You do not have permission to use the bulk load statement
Is it mandatory to assign sysadmin role also for bulk insertion? One more thing that if I use like
Database_name.dbo.tempLDRsup924 in query will solve his problem?
January 20, 2010 at 1:48 am
No reply yet..:crying:
January 20, 2010 at 6:41 am
I think it would be much easier, faster and more secure to create an SSIS package, have that package scheduled, and then allow the front-end to fire off the scheduled job.
If this is only a one time data pump, just create a simple SSIS package and then delete it.
Keep things simple
Andrew SQLDBA
January 20, 2010 at 12:47 pm
That error is fairly explanatory... the user does not have permission to use the bulk insert statement. Are you sure the use is the one attempting to execute the statement? You say you granted him db_owner, do you mean you made him a member of the db_owner role, or is that his default schema?
If the table is in tempdb, that db gets rebuilt after every reboot so any permissions you gave that user in that db will not persist (unless you also added him in model as db_owner which would probogate over to tempdb on reboot.
The probability of survival is inversely proportional to the angle of arrival.
January 20, 2010 at 1:31 pm
From BOL:
Requires INSERT and ADMINISTER BULK OPERATIONS permissions. Additionally, ALTER TABLE permission is required if one or more of the following is true:
Constraints exist and the CHECK_CONSTRAINTS option is not specified.
Note:
Disabling constraints is the default behavior. To check constraints explicitly, use the CHECK_CONSTRAINTS option.
Triggers exist and the FIRE_TRIGGER option is not specified.
Note:
By default, triggers are not fired. To fire triggers explicitly, use the FIRE_TRIGGER option.
You use the KEEPIDENTITY option to import identity value from data file.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply