Is it possible to use SqlBulkCopy in SQL 2008 without permission 'db_datawriter' ?

Question

I want to limit my database possible access ways to only using stored procedures. Everything works fine except System.Data.SqlClient.SqlBulkCopy. I'm using it only in one class for massive data import.

Is it possible to avoid this problem?

I tried to grant a right before calling SqlBulkCopy and remove it after:

EXEC [db_mod].[sys].[sp_addrolemember] N'db_datawriter', N'my_user'
EXEC [db_mod].[sys].[sp_droprolemember] N'db_datawriter', N'my_user'

but such approach raises an error:

User does not have permission to perform this action.

I'm executing it under the same user. How can I allow what I need?

Answer 1

The error described, following an attempt at sp_addrolemember(), is related to the current user not having the necessary privileges to alter account and security settings.

With regards to running SqlBulkCopy, it requires write privileges, and the db_datawriter role is a convenient way to provide these, but maybe you can consider alternatives. For example, rather than increasing (if only temporarilly) the security privileges of the current account, you can modify the security settings associated with this particular database/tables.

Changing the security settings of "securables" such as database objects, also requires some administrative privileges, but these may be associated with the database ownership, and hence may be available to you even though the privileges of changing an account are not.