August 19, 2014 at 8:36 am
"The SQL login requires 'DBowner' rights to temptb to allow Bulk Insert. The way that SQL works means that the user will already have read write access to tempdb. The reason we specifically need DBOwner rights is because of a confirmed microsoft limitation in their SQL product. Specifically BULK INSERT, requires db owner rights."
So.. apparently you need DBO rights on tempdb to do a bulk insert
August 19, 2014 at 8:42 am
Errr.... First time I've heard that.
Could you ask that person for the kb article for that 'confirmed microsoft limitation'?
According to Books Online (http://msdn.microsoft.com/en-us/library/ms188365.aspx)
BULK INSERT (Transact-SQL)
Imports a data file into a database table or view in a user-specified format.
Permissions
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.
Triggers exist and the FIRE_TRIGGER option is not specified.
You use the KEEPIDENTITY option to import identity value from data file.
No mention anywhere of TempDB permissions required.
Are they trying to BULK INSERT into a table in TempDB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2014 at 8:52 am
I have no idea what they are trying to do that was a requirement for a new project, I think they are slightly.. confused.
Unless I am missing something, bulk insert should let you use bulk insert on any DB, and why would you bulk insert into a table in tempdb? Strange
August 19, 2014 at 9:05 am
Could be a case of blaming the database for an error without investigating.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply