SqlBulkCopy permissions

[Bryce K. Nielsen]

We're using SqlBulkCopy for some large data migration routines, but the DBA
does not want to grant anything to our user account except the bare minimum
needed to execute SqlBulkCopy. There's nothing in the Help file about
required permissions, but reading online, it looked like you need INSERT on
the tables, and be in the ADMINISTER BULK OPERATIONS group. Our user is, but
we're still getting this error:

Cannot access destination table 'Table_Name'

What other permissions are needed to execute this properly?

-BKN

 

[Paul Clement]

¤ We're using SqlBulkCopy for some large data migration routines, but the DBA
¤ does not want to grant anything to our user account except the bare minimum
¤ needed to execute SqlBulkCopy. There's nothing in the Help file about
¤ required permissions, but reading online, it looked like you need INSERT on
¤ the tables, and be in the ADMINISTER BULK OPERATIONS group. Our user is, but
¤ we're still getting this error:
¤
¤ Cannot access destination table 'Table_Name'
¤
¤ What other permissions are needed to execute this properly?

From the SQL doc for BULK INSERT:

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.

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.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Bryce K. Nielsen]

Did you read my post? I'm not doing "bulk insert", I'm using SqlBulkCopy. I
also said the user already has INSERT and ADMINISTER BULK OPERATIONS and
it's not working. There are some other permissions needed.

As a side note, saying that SqlBulkCopy is the same as BULK INSERT can't be
right, since there are no "check_constraints", "fire_trigger", nor
"keepidentity" options on the SqlBulkCopy object (having access to that
would be nice, but it doesn't look like I have access to it).

So I ask again, what are the minimum permissions needed to run the ADO.NET
2.0 SqlBulkCopy object?

-BKN

 

[Bryce K. Nielsen]

Requires INSERT and ADMINISTER BULK OPERATIONS permissions. Additionally,

ALTER TABLE permission is
required if one or more of the following is true:

Is that even possible? I didn't think SQL Server allowed granting ALTER
TABLE, I thought you had to be owner of the table or database to be able to
do that...

-BKN

 

[Bryce K. Nielsen]

So I ask again, what are the minimum permissions needed to run the ADO.NET

2.0 SqlBulkCopy object?

Nothing works better than trial and error, I was able to figure out on my
own that all you need is INSERT and SELECT rights to the table in question.
You do *not* need any of the bcp administrative rights to get SqlBulkCopy to
work.

-BKN

 

[Paul Clement]

¤ Did you read my post? I'm not doing "bulk insert", I'm using SqlBulkCopy. I
¤ also said the user already has INSERT and ADMINISTER BULK OPERATIONS and
¤ it's not working. There are some other permissions needed.
¤

Sorry I didn't realize you were using the class. Of course I had also assumed that you had set
SELECT privileges on the table since it's rather difficult to do anything when you can't see it in
the first place. ;-)


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Bryce K. Nielsen]

Sorry I didn't realize you were using the class. Of course I had also

assumed that you had set
SELECT privileges on the table since it's rather difficult to do anything
when you can't see it in
the first place. ;-)

If I was the DBA, it would have SELECT privileges, but alas that is not the
case. Our DBA wants as limited access as possible. Elsewhere someone was
mentioning that if you wanted db-logging, you would probably also need
ADMINISTER BULK COPY, but we don't need that, so we're sticking with INSERT
and SELECT.

Thanks,

-BKN